Return cell(s) Conditional Formatting Status via a macro



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
If redcells > 0 Then
Sheets("Sheet1").Tab.ColorIndex = 3 'If any red, make tab red 3, done
If orangecells > 0 Then
Sheets("Sheet1").Tab.ColorIndex = 45 'Or if any orange, make tabe
orange 45, done
Sheets("Sheet1").Tab.ColorIndex = 50 'Else if any No color, make tab
green 50, done
End If
End If
End Sub


Thanks Bob for the link.
I Created a module and pasted the "CFColorCount" function into it.
I created another module to call the Function via a macro as follows:

Sub checkcolors()

Call CFColorCount(A1:A1,45)

End Sub

I get an "Expected: list seperator or )" error
I get a syntax error at the Call.

Any Ideas?

Thank You


You'll have to pass an actual Range, not just the reference to the

Dim rngRange as Range
set rngRange = Range("A1:A1")

Call CFColorCount(rngRange,45)

Probably will work with just:

Call CFColorCount(Range("A1:A1"),45)

but I like to pass a variable. :)



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question