Excel Count Cells By Color – Using VBA ColorIndex – CountByColor

Excel Count Colored Cells with VBA ColorIndex

To count colored cells in Excel using VBA color codes, use this code.

This code will check for VBA color index in each cell for the worksheet. To start with, color the cell A1 with red and run the macro. It will count all the cells with red background color and give the result.

If you want to count cell with different color, then change the color of cell A1. (example: Green, Yellow or any color from color palette. Here is the code.

How to Count Cells by Color? or Sum Cells by Color?

This one function in this section will CountbyColor and SumBycolor, both the functions based on the background color or Font color of a cell. The below function is a UDF that can be used in a Excel worksheet directly, once you copy paste this to a new module in VB editor.

Function VBA_Sum_Count_Cell_Color_Font_Color(rCells As Range, rReference As Range, sOption As String) As Long
Dim rCell As Range, rColIndex As Long, retVal As Long

'Get Option From the User
If sOption = "" Then
    MsgBox "Option is missing"
    Exit Function
End If
retVal = 0
sOption = VBA.UCase(sOption)

'Process Option and perform Sum of Count
Select Case sOption
    Case "COUNTCCOLOR"
        rColIndex = rReference.Interior.ColorIndex
        For Each rCell In rCells
            'COUNT Cells by Color Index of Reference Cell Background Colorindex
            If rCell.Interior.ColorIndex = rColIndex Then
                retVal = retVal + 1
            End If
        Next rCell
        
    Case "SUMCCOLOR"
        rColIndex = rReference.Interior.ColorIndex
        For Each rCell In rCells
            'SUM Cells by Color Index of Reference Cell Background Colorindex
            If rCell.Interior.ColorIndex = rColIndex Then
                retVal = retVal + rCell.Value
            End If
        Next rCell
        
    Case "COUNTFCOLOR"
        rColIndex = rReference.Font.Color
        For Each rCell In rCells
            'COUNT Cells by Color Index of Reference Cell Font Colorindex
            If rCell.Font.Color = rColIndex Then
                retVal = retVal + 1
            End If
        Next rCell

    Case "SUMFCOLOR"
        rColIndex = rReference.Font.Color
        For Each rCell In rCells
            'SUM Cells by Color Index of Reference Cell Font Colorindex
            If rCell.Font.Color = rColIndex Then
                retVal = retVal + rCell.Value
            End If
        Next rCell
    Case Else
        MsgBox "Incorrect option specified"
        Exit Function
End Select

'Return Sum or Count of Cells Matching Reference Cell ColorIndex
VBA_Sum_Count_Cell_Color_Font_Color = retVal
End Function
Function Count_Cell_Color(rCells As Range, rReference As Range) As Long
    Count_Cell_Color = VBA_Sum_Count_Cell_Color_Font_Color(rCells, rReference, "countCcolor")
End Function
Function Count_Font_Color(rCells As Range, rReference As Range) As Long
    Count_Font_Color = VBA_Sum_Count_Cell_Color_Font_Color(rCells, rReference, "countFcolor")
End Function
Function Sum_Cell_Color(rCells As Range, rReference As Range) As Long
    Sum_Cell_Color = VBA_Sum_Count_Cell_Color_Font_Color(rCells, rReference, "sumCcolor")
End Function
Function Sum_Font_Color(rCells As Range, rReference As Range) As Long
    Sum_Font_Color = VBA_Sum_Count_Cell_Color_Font_Color(rCells, rReference, "sumFcolor")
End Function

The main count by color function is ‘VBA_Sum_Count_Cell_Color_Font_Color’ which is called by 4 different functions at the bottom of the code. This will give you the understanding of the parameters used in this function.

If you want to know more about VBA Colorindex or Color codes, refer this link.