Msgbox if background color exists in range?

[9tanstaafl9]

Hey, sorry if this is dumb, I'm sleepy (it's 3:45am in Seattle). Is there an easy way to test to see if I have conditional formatting activated in any cells in a range?

Basically, if there are any cells highlighted in red (using the conditional formatting in excel 2003), I want a message box to pop up when they go to save saying something along the lines of, "Hey buddy, you really need to deal with this."

this is the last thing I need to do before I can go to sleep. Please help anybody!

Jennifer

 

[Norie]

Jennifer

Why not use the condtions you are using in the conditional formatting in a formula in a cell?

 

[shyrath]

As per Norie, would be easier - as you already know what causes the conditional formatting to kick-in.

But if you must : you could use something like this

Sub findcolor()

'put color in A99 to test
Range("A99").Interior.ColorIndex = 4

For Each c In Range("A1:A100")

If c.Interior.ColorIndex = 4 Then
    
    MsgBox c.Address & " is green"
    Exit For
    
    
End If

Next c

End Sub

 

[9tanstaafl9]

Ok, I know I'm being really dumb here, but how do i test for RED? What number is that?

This is what triggers the formatting:
=$I10="Y"

A99?

 

[james_lankford]

shyrath,
I know the color palettes can change, but has anyone ever made a list of constants to go with all the color number values for the default palette ?

so we could go
Range("A99").Interior.ColorIndex = =xlInteriorGreen

"how do i test for RED? What number is that ?"
there's probably an easier, but hte way I do it is to open a new workbook, recordd a macro in the new workbook and format a cell and set its pattern to the color I want, then stop recording the macro and look at the vba code

in this case, the index is 3

 

[9tanstaafl9]

Jennifer

Why not use the condtions you are using in the conditional formatting in a formula in a cell?

Because I dont' know how. While I've been able to figure some things out by reading this board, I have no other VBA experience, and there are some significant holes in my understanding of Excel. Honestly, I just discovered the conditional formatting feature.

Seven months in! I'm miles ahead of where I was, but there is SO far to go.

Thanks again for all your help.
Jennifer

 

[9tanstaafl9]

Could you tell me what color (number?) is default? Then I could just test for NOT that?

 

[Norie]

You don't count for red, use the formula or an equvalent - perhaps COUNTIF.

=COUNTIF($I10:I98, "Y")

 

[shyrath]

Red is 3
Lookie here
http://www.mvps.org/dmcritchie/excel/colors.htm

So, if the conditional formatting is triggered by =$I10="Y"

then you could do:

Sub testforY()

If Range("I10") = "Y" Then
    
    MsgBox "I10 is Y!"

End if
End Sub

Or if you insist in testing if it´s red

Sub testforY()

If Range("I10").Interior.ColorIndex = 3 Then
    
    MsgBox "I10 is Red!"

End if
End Sub

 

[9tanstaafl9]

Red is 3
Lookie here
http://www.mvps.org/dmcritchie/excel/colors.htm

So, if the conditional formatting is triggered by =$I10="Y"

then you could do:

Sub testforY()
 
If Range("I10") = "Y" Then
 
    MsgBox "I10 is Y!"
 
End if
End Sub

Or if you insist in testing if it´s red

Sub testforY()
 
If Range("I10").Interior.ColorIndex = 3 Then
 
    MsgBox "I10 is Red!"
 
End if
End Sub

Again, I apologize for being dense, but I want to test the whole column for red, not just that one particular cell. When I did the conditiona formatting, it automatically adjusts for each row. How would I do that in VBA ising the Range("I10") -- which does not happen to be red anyway.

That's why I was thinking I could just look for anything red in that range (it's 1 column, includes blanks, and MAYBE one or more cells that are red).

????