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

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.
    • 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:

    Count cells by color in Excel
    Count cells by color in Excel
    See also  Lesson#214: Deleting Blank Rows in a Specified Range Using VBA

    Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    *