Return cell(s) Conditional Formatting Status via a macro

[JudgeMental]

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

 

[Bob Phillips]

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]

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
or
I get a syntax error at the Call.

Any Ideas?

Thank You

 

[chip.gorman]

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

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.

HTH

Chip