Count coloured cells

[mart10]

I am using Excel 2003 SP3

Quite often, in fact very often when flagging a spreadsheet by coulouring cells I want to do a count on the colour. I end up just creating another column and putting in a flag code (by colour) and then doing a count based upon filtered results

Is there a VB or other method, Is this functionality of later versions of Excel?

 

[SkipVought]

hi,

Excel uses VALUES to function most effectively. I'd recommend using values to regulate your colors, via Conditional Formatting. Unfortunately 2003 has only THREE CF options. 2007+ has as many as you can handle.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Alternatively, if you want to actually count the number of cells in a given range which have a specific colour, you could use the following user-defined function:

Public Function CountColour(rng As Range, r As Byte, g As Byte, b As Byte)
Dim cnt As Long, clr As Long
Dim c As Range
clr = RGB(r, g, b)
For Each c In rng
    If c.Interior.Color = clr Then cnt = cnt + 1
Next c
CountColour = cnt
End Function

Paste the above code into a module.

If, in cell A1, you want to display the number of cells in the range B1:C20 which are coloured red, then, in cell A1, enter:
=CountColour(B1:C20,255,0,0)

Does this help?

Tony