This is a simple Function that is easy to add to Excel and just works. It counts the cells that match the colour in the second parameter. No fuss, no muss...
Warning: if you just change the colour of a cell, press F9 to RECALCULATE the sheet totals.
Otherwise, it is totally awesome. Thanks Jules. Steve
COUNTIF on cell color - Microsoft Answers:Excel has no native way of doing this so we must create a UDF to do it. ALT+F11 to open vb editor, right click 'ThisWorkbook' and insert module and paste the code below in. Close VB editor.
Back on the worksheet call with
=Countcolour($A$1:$J$14,K1)
Where:-
A1:J1 is the range you want to count
K1 is a cell with the fill colour you want to count.
Function Countcolour(rng As Range, colour As Range) As Long
Dim c as Range
Application.Volatile
For Each c In rng
If c.Interior.ColorIndex = colour.Interior.ColorIndex Then
Countcolour = Countcolour + 1
End If
Next
End Function
