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

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

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

"Bob Phillips" wrote:

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

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


On Mar 15, 12:54 pm, JudgeMental
wrote:
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

"Bob Phillips" wrote:
Seehttp://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