See http://www.xldynamic.com/source/xld.CFConditions.html

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JudgeMental" wrote in message
...
Using a macro, I would like to read the Conditional Formatting status of
cells so that I can, for example: color a sheets tab red if any cells on
the
sheet are out of range based on their conditional format status. Attached
is
code that I thought would do the trick. It works if I manually set the
cells
to colors 3 or 45, but always returns -4142 if cell colors are set via
Conditional Formatting.

Sub Sheet1_ColorCellsTest()
Dim redcells As Integer
Dim orangecells As Integer
redcells = 0
orangecells = 0
For Each c In Worksheets("Sheet1").Range("D7,E12:E44").Cells
Select Case c.FormatConditions(1)
Case 3 '3 red
redcells = redcells + 1
Case 45 '45 orange
orangecells = orangecells + 1
Case Else 'Not red or orange
redcells = redcells
orangecells = orangecells
End Select
Next
If redcells 0 Then
Sheets("Sheet1").Tab.ColorIndex = 3 'If any red, make tab red 3, done
Else
If orangecells 0 Then
Sheets("Sheet1").Tab.ColorIndex = 45 'Or if any orange, make tabe
orange 45, done
Else
Sheets("Sheet1").Tab.ColorIndex = 50 'Else if any No color, make
tab
green 50, done
End If
End If
End Sub