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.

CountIF(A01:A55,"backgroundcolour=red")

---

---

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:

=CountColor(A1:A99,D2)

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