Sadly I don't think this is as easy as you'd expect. Excel/vba will not recognize the conditionally formatted color as the true color of the cell. E.g. try
Debug.Print [aq1].Interior.Color
Debug.Print [g5].Interior.Color
and they won't match up. Otherwise, the code below would work fine. Maybe someone has another idea but the only way I can see of making this work is having a vba code that evaluates the conditional formatting criteria directly, and pastes the colour as the true colour of the cell.
Option Explicit
Sub ColourCounter()
Dim i As Integer, columnblock As Integer, columnoffset As Integer, rownumber As Integer
Dim greencount As Integer, redcount As Integer, yellowcount As Integer
For i = 1 To 3
columnblock = 13 * (i - 1) + 7 + (i - 1)
For columnoffset = 0 To 6 Step 3
For rownumber = 5 To Cells(Rows.Count, columnblock + columnoffset).End(xlUp).Row
If Cells(rownumber, columnblock + columnoffset).Interior.ColorIndex = [aq1].Interior.ColorIndex Then
greencount = greencount + 1
ElseIf Cells(rownumber, columnblock + columnoffset).Interior.ColorIndex = [aq2].Interior.ColorIndex Then
yellowcount = yellowcount + 1
ElseIf Cells(rownumber, columnblock + columnoffset).Interior.ColorIndex = [aq3].Interior.ColorIndex Then
redcount = redcount + 1
End If
Next rownumber
Next columnoffset
Next i
'pastes counts of each colour onto worksheet
[ar12].Value = greencount
[ar13].Value = yellowcount
[ar14].Value = redcount
End Sub
Bookmarks