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]

Whole colmun formula.:

=COUNTIF(I:I, "Y")

 

[9tanstaafl9]

I was trying something like this. What am I doing wrong:

For Each c In Range("LT1")
If c.Interior.ColorIndex = 3 Then
MsgBox "WARNING! You have one or more serious impact issues on the LT tab."
Exit For
End If

 

[9tanstaafl9]

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

Is it possible that conditional formatting isn't really setting the interior color and that there's something else i need to test? I don't see why my attempts aren't working.

Dim c As Range
For Each c In Range("LT1")
If c.Interior.ColorIndex = 3 Then
    MsgBox "WARNING! You have one or more serious IMPACT ISSUES on the Lead Time tab."
    Exit For
End If
Next c

If I replace =3 with <>0 that DOES trigger the msgbox, but it does it regardless of if all cells are white or red, or whatever. If I use = 0 it doesn't trigger at all.

Could it have anything to do with the fact that there are two other conditional formats applied to the cell? It either turns white or red or orange.

 

[shyrath]

Sorry my bad

this will look through column I from first cell to last entry in column. And will stop with msgbox on 1st match

Sub testforY()

lastrow = Worksheets("Sheet1").Range("I65536").End(xlUp).Row
rng = "I2:I" & lastrow

For Each c In Range(rng)

If c.Value = "Y" Then
    MsgBox c.Address & " is Y, pls fix"
    Exit For
End If

Next c

End Sub

 

[9tanstaafl9]

Sorry my bad [/code]

You're not bad, you're just overestimating my skills, which would be hard not to do.

That worked! I'll just have to modify it so it can look for a Y both I and J, but I should be able to figure that out myself.

Thank you, I really appreciate the help. I especially like that lastrow bit, I'll definately be using that. Thanks!
Jennifer

 

[MickG]

Hi, Just to confuse you more, you could try this.
I'm not really up to speed on whats gone before but, I think you will find you can not Get the interior colour index number of a cell when it is "Conditionall formatted".
But this code will find whether any one of the three Possible condition (1 to 3) are met.
If your Colour Red, is Condition (1) then this code will tell you How many cells are coloured Red. If you want ether of the other two condition ,Change the"a" to "b" or "c".
Note:- For this code to work The "Red" Cells must be all the Same Condition Number!
As you will see it is a Workbook_BeforeClose" Event, But you could test it in a "Button"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim a As Integer, b As Integer, c As Integer, Res As String
Dim Rng As Range, Dn As Range, col As Integer, ocol As Integer
On Error Resume Next
Application.ScreenUpdating = False
Set Rng = ActiveCell.SpecialCells(xlCellTypeAllFormatConditions)
For Each Dn In Rng
 ocol = 0
 Dn.Select
  
    For col = 1 To 3
             Res = Evaluate(Dn.FormatConditions(col).Formula1)
          If Res = True Then
              ocol = Dn.FormatConditions(col).Interior.ColorIndex
             Exit For
          End If
Next col

Select Case ocol
Case Is = Dn.FormatConditions(1).Interior.ColorIndex
a = a + 1
Cells(a, "G") = Dn.Address
Case Is = Dn.FormatConditions(2).Interior.ColorIndex
b = b + 1
Cells(b, "i") = Dn.Address
Case Is = Dn.FormatConditions(3).Interior.ColorIndex
c = c + 1
Cells(c, "j") = Dn.Address
End Select

Next Dn
MsgBox "Problem !!, You Have " & a & " Cells Coloured Red"
'MsgBox "Colour Red = " & a & Chr(10) & "Colour Green = " & b & Chr(10) & "Colour Blue = " & c

Application.ScreenUpdating = True
End Sub

Regards Mick