This could me the macro that does the trick for counting the cells by colour.
"no" is a variable that in my working workbook I will need to work out, this I can do I sure.
the colour "index" again I will know in advance although I wount know the range or even if it exists. this needs to be played with a bit more to see if I can put a loop into the macro and make the colourindex a variable. this would allow me to run the one macro across 20 rows and count each consecutive row of the same colour and then when the colour changes. if there are two colours such as red and green i'll have one number for red and one for green. this may stop me having to have a macro to count each colour. as I say this needs to be played with and experimented with but its looking good
the cell references differ from the workbook I posted but I hope you can follow the code.
Dim colourCellCount As Integer
Dim no As Long
no = 13 ' this would be the whole range
colourCellCount = 0
Range("H2").Select ' this is where to start counting from
For N = 1 To no ' no is dimmed as long and is the whole range
If Selection.Interior.Color = 255 Then ' the 255 is the colour index of the red
' If Selection.Interior.ThemeColor = xlThemeColorAccent3 Then 'xlThemeColorAccent3 is the colour code for the light green in my workbook
colourCellCount = colourCellCount + 1
ActiveCell.FormulaR1C1 = colourCellCount