Dear Readers,

Last Friday, my wife, Shagun Sharma, who is a Vlogger shared an excel sheet with some data and a problem to solve, which can save some time of hers.

Excel formula to count cells with specific colors

In order to count all such cells with a specific background color, I defined a user-defined function. to count the number of cells with a specific background color.

The background color of a cell is stored in cell.Interior.ColorIndex in Excel VBA. This ColorIndex, as the name suggests stores the index in a numerical value assigned for each color. Therefore, we start counting, we need to define a function that can return you the ColorIndex of the background color of a cell.

VBA to get the Color Index of background color of a cell

Following is the simple, user-defined function, that will return the ColorIndex of provided cell address

Public Function getColorIndex(ByVal cell As Range) As Integer
    getColorIndex = cell.Interior.ColorIndex
End Function

After running the above custom formula, you can see the color index of green color is returned i.e. 50.

Cell Interior color index

Using the above custom function, now we can define another customer formula, which can return the count.

Public Function getColorCount(ByVal cell As Range, ByVal hex As Long) As Integer
    Count = 0
    For Each cell In cell.Cells
        If (cell.Interior.ColorIndex = hex) Then
            Count = Count + 1
        End If
    Next
    getColorCount = Count
End Function

After running the above custom formula, you can get the count of green color cells.

Get Count of colored background