MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Counting Cells by Interior Color Using VBA

April 17, 2002 - by Bill Jelen

Emily asks:

Please could you advise the formular solution for counting the number of cells that have a particular backgorund colour (i.e. these cells contain some data however the colour is what's important in order to count them).

Logically I feel it should follow a standard formular i.e.


I believe the colour red should be identified by the number 3 (according the the help file)

I need to know how to code the background cell colour i.e. colorfill?, backgroundcolour?, interior.color?...???

Emily - if only it were that easy. You need to have a User Defined Function in VBA. Below is the code for the function. You need to enter this in a new code module in the workbook.

Once you have the code entered, you can use this formula:


Where D2 has the background format that you want to count.

Here is the code:

Function CountColor(Rng As Range, RngColor As Range) As Integer
    Dim Cll As Range
    Dim Clr As Long
    Clr = RngColor.Range("A1").Interior.Color
    For Each Cll In Rng
        If Cll.Interior.Color = Clr Then
            CountColor = CountColor + 1
        End If
    Next Cll
End Function

Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.