Change color of Cell based on another cell´s color

April 12, 2012 at 08:45:50
Specs: Windows 7
Hi,

I have an excel file with 2 sheets:

Sheet 1 has Real numbers of a business performance for the year X in range (D17:V113) and the variation vs Sheet 2 in % in range (Y17:AQ113)

This variation cells in range (Y17:AQ113) change their color based on the intensity of the variation using conditional formatting

Now the cells in range (D17:V113), which are supposed to be entered by the user, should adopt the color of its counterpart in range (Y17:AQ113) and for that I saw in another post that some VBA is needed.

Then, I believe the code should look something like this:

Sub MatchColors()
For Each myCellColor In Range("Y17:AQ113")
myCellColor.Offset(0, -21).Interior.ColorIndex = _
myCellColor.Interior.ColorIndex
Next
End Sub

But the thing is that I am a total newbie at VBA and don´t really know how to get that code running.

Help with this last point would be greatly aprreciated!

Thanks!


See More: Change color of Cell based on another cell´s color

Report •

#1
April 12, 2012 at 09:34:18
The Interior.ColorIndex does not return the Conditionally Formatted color of a cell.

It only returns the ColorIndex of cell that has been Filled either manually or by setting the Interior.ColorIndex via VBA.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 12, 2012 at 11:29:35
Thanks for your answer DerbyDa03

I´ve considering 2 options

(1) Build a macro that substitutes for the conditional formatting and then that one I posted before should work

-> I have tried this, the problem I find is that it doesn´t jump automatically whenever values change (the values that were originally with conditional formatting and now with the macro) -> A change in any of these values should trigger both of the macros -> Do you know how to accomplish this?

(2) Build a macro that can retrieve the color of a cell set by conditional formatting

-> I believe this is much tougher. However I found on the net a function already defined for this, at least partially. I say partially because according to its definition it returns the color of the first cell in the range that it takes as an argument

So I could either modify the function so it takes a single cell as an argument (because it will go under the for loop) or modify the routine MatchColors so it explores the range in a way that it takes subranges where in every iteration the first cell in the range follows the exploration pattern of the For

-> Do you know if the For first goes by rows and then by columns, or the other way?

Thanks for your time!


Report •

#3
April 12, 2012 at 12:19:39
re: -> Do you know if the For first goes by rows and then by columns, or the other way?

A great way to learn VBA is to write little routines to test things. Try this:

Sub WhatOrder()
 For Each myCell In Range("A1:C3")
   cellNum = cellNum + 1
   myCell.Value = cellNum
 Next
End Sub

re: A change in any of these values should trigger both of the macros

If you want a macro to fire when a value in a cell changes, you should consider a WorksheetChange macro.

For example, right-click a sheet tab and choose View Code. Paste this code into the VBA editor and change any cell in that sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
 MsgBox "The address of the cell you changed is " & Target.Address
End Sub

You can control the range that the macro will work within by testing the address (or column or row or range) as soon as the change is made.

Private Sub Worksheet_Change(ByVal Target As Range)
'Perform actions if changed cell is in Row 1
  If Target.Address = "$B$2" Then
   MsgBox "The cell you changed is " & Target.Address
   Target.Interior.ColorIndex = 5
  End If
'Perform actions if changed cell is in Row 1
  If Target.Row = 1 Then
   MsgBox "The cell you changed is in Row " & Target.Row
  End If
'Perform actions if changed cell is in Column D
  If Target.Column = 4 Then
   MsgBox "The cell you changed is in Column " & Target.Column
  End If
'Perform actions if changed cell is in Range A3:C47
  If Not Intersect(Target, Range("A3:C47")) Is Nothing Then
   MsgBox "The cell you changed is within A3:C47"
  End If
End Sub

You can only have one WorksheetChange macro per sheet, so if you want different things to happen depending a where the change was made, then you have to do multiple tests like I did above.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 13, 2012 at 03:03:51
Thanks a lot again!

Will give it a try and get back as soon as I have time!


Report •

Ask Question