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

 

[foozay]

how would i check if this is true for cells B2 to S2 in a CF in A2: and(b2>today(), b2-today() <=30)

 

[SamTYler]

I see what yu are saying. I just learned something new!!! Thanls.

CF does not effect the .Interior.ColorIndex of a Cell.

I didn't know that before today. Maybe we should call it Phantom Formatting instead of Conditional Formatting.

How about this? Will it work?

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.FormatConditions(1).Interior.ColorIndex = 3 Then 
      Range("A2".Interior.ColorIndex = 3
      Exit Sub
   Else
      Range("A2").Interior.ColorIndex = xlColorIndexNone
   End If
Next Cel
End Sub

It works on mine, but I only set one condition.

You'll have to change the FormatConditions index to match the conditional format # that turns the cells red

 

[tusharm]

Why persist with code when it is trivially solved with the c.f. formula
=SUM(($B$2:$G$2>TODAY())*($B$2:$G$2<=TODAY()+30))>0

I see what yu are saying. I just learned something new!!! Thanls.

CF does not effect the .Interior.ColorIndex of a Cell.

I didn't know that before today. Maybe we should call it Phantom Formatting instead of Conditional Formatting.

How about this? Will it work?

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.FormatConditions(1).Interior.ColorIndex = 3 Then 
      Range("A2".Interior.ColorIndex = 3
      Exit Sub
   Else
      Range("A2").Interior.ColorIndex = xlColorIndexNone
   End If
Next Cel
End Sub

It works on mine, but I only set one condition.

You'll have to change the FormatConditions index to match the conditional format # that turns the cells red

 

[wigi]

Why persist with code when it is trivially solved with the c.f. formula

I don't understand why you choose VBA for this kind of task.

I showed you a simple alternative for the CF formula, post # 6. If this is not what you wanted, please comment on that formula, instead of writing / trying to write a bunch of VBA-code.

 

[foozay]

thanks a lot tusharm, that worked

but could you do me a favor and explain what that does, please

also i heard you cant call another sheet in a CF. how would i use that formula but make another cell in a different sheet change to red

 

[foozay]

thanks for your help guys

 

[tusharm]

It's an array formula that tests each cell in the range with the required condition and generates a non-zero result if there is at least one match. To see the formula in action, enter it as an array formula in some cell. Then, use the 'Evaluate Formula' Excel capability to see how Excel calculates the result.

To enter an array formula complete data entry with the CTRL+SHIFT+ENTER key combination. If done correctly, *Excel* will show the formula enclosed in curly brackets { and }

thanks a lot tusharm, that worked

but could you do me a favor and explain what that does, please

also i heard you cant call another sheet in a CF. how would i use that formula but make another cell in a different sheet change to red

 

[SamTYler]

Tusharm,

Why persist with code when it is trivially solved with the c.f. formula

I like code. What is your problem that you have to attack me?

Wigi,

I don't know if you are responding to Tusharm, Foozay, or me. I did the code, Tusharm did one CF formula, you did another, and we are all trying to help Foozay.

Isn't it amazing how many ways there are to do the same thing?

 

[foozay]

thanks a lot for your help guys. it worked wonderfully

NOW.... I wanna do the same thing but A2 is on a different worksheet

lol thats the last thing i will need .... i think ... hopefully

 

[foozay]

thanks a lot for your help guys. it worked wonderfully

NOW.... I wanna do the same thing but A2 is on a different worksheet

lol thats the last thing i will need .... i think ... hopefully