Counting Cells by Interior Color Using VBA
April 17, 2002 - by Bill Jelen
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