Count the Number of Colored Cells in Excel

First open the workbook in which you wish to count or sum cells by a fill color. Now go into Visual Basic. Insert new module:

Code:

Funtion ColorCount(rColor As Range, rRange as Range, Optional Sum As Boolean)
Dim rCell as Range
Dim lCol as Range
Dim vResult


1Col = rColor.Interior.ColorIndex

If Sum = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.Sum(rCell, vResult)
End If
Next rCell

Else

For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell

End If

COlorFunction = vResult

End Function

--------------------------------------------------------

You can use the function that was just created by inputing this code into a cell

=ColorFunction($C$1,$A$1:$A$12,TRUE) to sum the values in the A:A column Range that have the same fill color as cell $C$1. The reason it will sum in because the code will have True as the last argument for the custom function.