vanhunk
04-15-2014, 06:31 AM
Counting the conditional format colours, column by column:
I have a table with columns representing months and rows some measurement. Each cell in the table are conditionally formatted, using formulas. What I need to do is count all the visible green, all the visible orange, and all the visible red cells per column. I have attached a file that does that for a selection. What I need is for the macro to step through the table, column by column and put the 3 results for each column below it.
This is the code thus far, it does the calculation for a selection only:
Sub SumCountByConditionalFormat()
Dim GreenRefColor As Long
Dim OrangeRefColor As Long
Dim RedRefColor As Long
Dim cntGreen As Long
Dim cntOrange As Long
Dim cntRed As Long
Dim i As Integer
Dim cellCurrent As Range
Dim cntCells As Long
cntGreen = 0
cntOrange = 0
cntRed = 0
i = 0
cntCells = Selection.CountLarge
GreenRefColor = Range("D65").DisplayFormat.Interior.Color
OrangeRefColor = Range("D66").DisplayFormat.Interior.Color
RedRefColor = Range("D67").DisplayFormat.Interior.Color
For i = 1 To (cntCells - 1)
If GreenRefColor = Selection(i).DisplayFormat.Interior.Color Then
cntGreen = cntGreen + 1
End If
If OrangeRefColor = Selection(i).DisplayFormat.Interior.Color Then
cntOrange = cntOrange + 1
End If
If RedRefColor = Selection(i).DisplayFormat.Interior.Color Then
cntRed = cntRed + 1
End If
Next
Range("E65") = cntGreen
Range("E66") = cntOrange
Range("E67") = cntRed
End Sub
I appreciate any help.
Thank you very much!
I have a table with columns representing months and rows some measurement. Each cell in the table are conditionally formatted, using formulas. What I need to do is count all the visible green, all the visible orange, and all the visible red cells per column. I have attached a file that does that for a selection. What I need is for the macro to step through the table, column by column and put the 3 results for each column below it.
This is the code thus far, it does the calculation for a selection only:
Sub SumCountByConditionalFormat()
Dim GreenRefColor As Long
Dim OrangeRefColor As Long
Dim RedRefColor As Long
Dim cntGreen As Long
Dim cntOrange As Long
Dim cntRed As Long
Dim i As Integer
Dim cellCurrent As Range
Dim cntCells As Long
cntGreen = 0
cntOrange = 0
cntRed = 0
i = 0
cntCells = Selection.CountLarge
GreenRefColor = Range("D65").DisplayFormat.Interior.Color
OrangeRefColor = Range("D66").DisplayFormat.Interior.Color
RedRefColor = Range("D67").DisplayFormat.Interior.Color
For i = 1 To (cntCells - 1)
If GreenRefColor = Selection(i).DisplayFormat.Interior.Color Then
cntGreen = cntGreen + 1
End If
If OrangeRefColor = Selection(i).DisplayFormat.Interior.Color Then
cntOrange = cntOrange + 1
End If
If RedRefColor = Selection(i).DisplayFormat.Interior.Color Then
cntRed = cntRed + 1
End If
Next
Range("E65") = cntGreen
Range("E66") = cntOrange
Range("E67") = cntRed
End Sub
I appreciate any help.
Thank you very much!