Excel VBA & Conditional Formatting

[Stefan]

I have used Format|Conditional Formatting on a range of
cells to change the cell fill colour when the cell value
exceeds a limit

Now I need to find out which cells have had this
formatting applied to them

It seems that
.Cells(Row, column).Interior.Color
.Cells(Row, column).Interior.ColorIndex
.Cells(Row, column).Interior.PatternColor
.Cells(Row, column).Interior.PatternColorIndex

only apply to the cell when you actually change the
formatting of the cell

ie it is as if the conditional formatting is an overlay


I am trying to build a Summary macro which alerts the user
to sheets on which the limits have been exceeded

Thanks
Stefan

 

[Bob Phillips]

Stefan,

You are right, you can't get the colour that way.

One way is to test the format condition colour, like so

With ActiveCell
On Error GoTo no_CF
If .FormatConditions(1).Interior.ColorIndex = 35 Then
MsgBox "found"
End If
End With
GoTo CF

no_CF: