Lesson#179: Count cells by color in Excel from a reference cell color with VBA

Here I shall make a VBA code to Count cells by color in Excel from a reference cell color.

1. Open the VBA editor: In Excel, press Alt + F11 to open the VBA editor.
2. Create a new module: In the VBA editor, go to the Insert menu and select Module. This will create a new module where you can write your VBA code.

Here is a function in VBA that you can use to count the number of cells in a specified range that have the same interior color as a reference cell:

Function CountMatchingInteriorColors(referenceCell As Range, targetRange As Range) As Long
    ' Get the interior color of the reference cell
    Dim referenceColor As Long
    referenceColor = referenceCell.Interior.Color

    ' Initialize a counter to zero
    Dim count As Long
    count = 0

    ' Loop through the cells in the target range
    Dim cell As Range
    For Each cell In targetRange
        ' If the cell's interior color matches the reference color, increment the counter
        If cell.Interior.Color = referenceColor Then
            count = count + 1
        End If
    Next cell

    ' Return the count
    CountMatchingInteriorColors = count
End Function

To use this function,

= CountMatchingInteriorColors(referenceCell, targetRange)

you can call it from your VBA code and pass it a reference cell and a target range as arguments. For example: