Counting Cell Background Colors

I have had several requests over the years for a function to count cells based on their background color.
Here is my solution. 2 Custom Functions.

If you dont know how to add a new function I will cover this in a later blog but for today we will skip that part.

The first function is CellColor and will help you identify the color index of a cell.

Function CellColor(CRange As Range)
 Dim cell As Range
        For Each cell In CRange
         CellColor = cell.Interior.ColorIndex
        Next cell
End Function

In the image above Cell B2 has the formula =Cellcolor(A2) which returns 38. Now we know what were looking for.

Now that we know the color of a cell we can use the next function CountColor to count the cells.

Function CountColor(CRange As Range, Color As Integer)
 Dim Ccount As Integer
 Dim cell As Range
        For Each cell In CRange
         If cell.Interior.ColorIndex = Color Then
         Ccount = Ccount + 1
         End If
        Next cell
        CountColor = Ccount  
End Function

 In the image above Cell B23 has the forumula =countcolor($A$2:$A$22,38) which returns 10.

Top Tip: Use them together and you don't have to remember that 38 is the index color of red.
=countcolor(A2:A22,CELLcolor(A2)) will return 10 as well.