I had few people ask me if there is a way in Excel 2007 to count cells with a certain colour. Excel 2007 doesn't have a build in function to do this - Excel 2007 has functions to count cells with numbers, cells that are not blank and some other count functions.
But you can easily add this functionality to your Excel book by writing your own custom function.
Start by bringing up the VBA editor (ALT+ F11) and in a new module add a macro and copy the code below. Then in an Excel book on sheet 1 enter the function like you would with any built in functions like this =countColor(range to count, cells with colour to count). You should see this new function (countColor) appears in the list of available functions.
How to use it:
"Range to count" is simply the cells you want to count the desired colour, simply drag the mouse over the range you want to count.
"Cells with colour to count", simply select one cells that contains the colour you want to count.
So the function will look something like this =countColor(B3:T3,$I$5), where B3 to T3 the range to count the colour and cell I5 contains a sample of the colour to count.
The code:
Function countColor(r As Range, c As Range) As Integer
Application.Volatile
Dim rr As Range
Dim tot As Integer
col = c.Interior.Color
For Each rr In r
If rr.Interior.Color = col Then tot = tot + 1
Next rr
countColor = tot
End Function
Finally to make this run properly we need to trigger this macro somehow, what I did is added the below code to the Sheet 1 module in the VBA editor. This will make Excel calculate every time you click on different cells causing our function to update.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub
