Msgbox if background color exists in range?

9tanstaafl9

Well-known Member
Joined
Mar 23, 2008
Messages
535
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows
Jennifer

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

shyrath

Active Member
Joined
Jun 28, 2005
Messages
483
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

Code:
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

Well-known Member
Joined
Mar 23, 2008
Messages
535
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

Well-known Member
Joined
Jan 11, 2009
Messages
1,181

ADVERTISEMENT

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
 
Last edited:

9tanstaafl9

Well-known Member
Joined
Mar 23, 2008
Messages
535
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

Well-known Member
Joined
Mar 23, 2008
Messages
535

ADVERTISEMENT

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,278
Office Version
  1. 365
Platform
  1. Windows
You don't count for red, use the formula or an equvalent - perhaps COUNTIF.

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

shyrath

Active Member
Joined
Jun 28, 2005
Messages
483
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:

Code:
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
Code:
Sub testforY()

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

End if
End Sub
 

9tanstaafl9

Well-known Member
Joined
Mar 23, 2008
Messages
535
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:

Code:
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
Code:
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).

????
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top