Changing Color of a Cell

[foozay]

Hi

I need help!! I am trying to change the color of a cell depending on the cells next to it.

Example.

Change cell A2 to red if any of the cells from B2:S2 are red

Additional info:

cells B2:S2 change color depending on what they contain - either red,yellow, or orange - I used conditional formatting for that

cells B2:S2 contain dates - depending on how close the date is to todays date, the color will change ( like a warning system)

Thank you for your help

 

[SamTYler]

Put this in the Worksheet Codepage

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel as Range
Dim myRange As Range
Set myRange = Range("B2:S2")
If Intersect(Target, myRange) is Nothing Then Exit Sub
 
For Each Cel in myRange
   IF Cel.Interior.ColorIndex = 3 Then 
      Range("A2".Interior.ColorIndex = 3
      Exit Sub
   Else
      Range("A2").Interior.ColorIndex = xlColorIndexNone
   End If
Next Cel
End Sub

 

[wigi]

Then, write conditional formatting for A2 as well.

But don't rely on the colors of the other cells (since grabbing them is difficult).

The CF in A2 should be based on the rules that determine the CF for the cells B2:S2.

 

[foozay]

thanks guys,

how do i call the function?
Do i create a module and then paste it?
the cf for b2:s2 are as follows:

example cell
1 and(b2>0, B2<today()) make it red
2 and(b2>today(), b2-today() <=30) make it orange
3 and(b2>today(), b2-today() <=90) make it yellow

i tried to put that in the cf for A2 as :

1 and(b2:s2>0, b2:s2<today()) make it red ... etc.

 

[tusharm]

As wigi pointed out use a c.f. with a formula that checks the values in the other cells. FWIR, SamT's code will not work. C.f. changes do not show up through the interior.colorindex property.

thanks guys,

how do i call the function?
Do i create a module and then paste it?
the cf for b2:s2 are as follows:

example cell
1 and(b2>0, B2<today()) make it red
2 and(b2>today(), b2-today() <=30) make it orange
3 and(b2>today(), b2-today() <=90) make it yellow

i tried to put that in the cf for A2 as :

1 and(b2:s2>0, b2:s2<today()) make it red ... etc.

 

[wigi]

The CF rules were cut off by the software of the board, but presumably you will want a CF in A2:

=MIN(B2:S2)<=TODAY()

 

[wigi]

As wigi pointed out use a c.f. with a formula that checks the values in the other cells. FWIR, SamT's code will not work. C.f. changes do not show up through the interior.colorindex property.

Hello Tushar

I think Sam wants to do all of the coloring with VBA code, no CF at all. I don't take this point of view .

 

[SamTYler]

FWIR, SamT's code will not work. C.f. changes do not show up through the interior.colorindex property.

All the color changes are driven by workbook changes. The first Value change that occured in B2:S2 would start the cycle.

 

[wigi]

But your code does not address the CF in the range B2:S2, only the effect of the colors on cell A2.

 

[SamTYler]

how do i call the function?
Do i create a module and then paste it?

It autoruns on any change in the values in any workshhet cell.

No, paste it into the codepage of the worksheet that holds the relevant cells.

Open the worksheet, then tap alt+F11. that should open the relevant codepage. You can verify the correct codepage by seeing the worksheet name highlighted in the VBA Project Explorer. That's available from the VBA Menu: View.