J
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
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