I got a question about counting background colors in a cell range. Excel uses two different properties to color cells and they are ColorIndex and Color property.

The ColorIndex property has 56 different colors, shown below.

The color property holds up to 16 777 216 colors. I tried to color 16 columns with 1048576 rows each (16 * 1048576 = 16 777 216) using the color property but excel returned this error after 65277 cells.

The following macro lets you count background colors, however it won't count cells colored with conditional formatting.

Sub CountColors()
'This macro counts background colors in cell range
'https://www.get-digital-help.com/2017/03/30/count-colored-cells/
Dim IntColors() As Long, i As Integer
Dim chk As Boolean
Set rng = Application.InputBox("Select a cell range to count colors: ", , , , , , , 8)
ReDim IntColors(0 To 2, 0)

For Each cell In rng
chk = False
For c = LBound(IntColors, 2) To UBound(IntColors, 2)
If cell.Interior.ColorIndex = IntColors(0, c) And cell.Interior.Color = IntColors(1, c) Then
IntColors(2, c) = IntColors(2, c) + 1
chk = True
Exit For
End If
Next c

If chk = False Then
IntColors(0, UBound(IntColors, 2)) = cell.Interior.ColorIndex
IntColors(1, UBound(IntColors, 2)) = cell.Interior.Color
ReDim Preserve IntColors(2, UBound(IntColors, 2) + 1)
End If

Next cell

ReDim Preserve IntColors(2, UBound(IntColors, 2) - 1)

Set WS = Sheets.Add

WS.Range("A1") = "Color and count"
WS.Range("B1") = "ColorIndex"
WS.Range("C1") = "Color"
j = 1
For i = LBound(IntColors, 2) To UBound(IntColors, 2)
If IntColors(2, i) <> 0 Then
WS.Range("A1").Offset(j).Interior.ColorIndex = IntColors(0, i)
WS.Range("A1").Offset(j).Interior.Color = IntColors(1, i)
WS.Range("A1").Offset(j) = IntColors(2, i)
WS.Range("A1").Offset(j, 1) = IntColors(0, i)
WS.Range("A1").Offset(j, 2) = IntColors(1, i)
j = j + 1
End If
Next i
End Sub

Where do I copy and paste the vba code?

1. Select and copy code above (Ctrl+c)
2. Open VB Editor (Alt+F11)
3. Insert a new module to your workbook
4. Paste code to code module

Instructions

1. Press Alt + F8 to see a list of macros
2. Select CountColors
3. Press "Run" button
4. Select a cell range you want to count

The macro then creates a new sheet with cells in a column colored and their count, see picture below.

Value -4142  means No fill and -4105 is default color (white).

There is no way to quickly transfer cell formatting properties to an array so the macro is quite slow, it reads a cells property one by one. I don't recommend using this with larger cell ranges unless you are prepared to wait for a while.

---

Weekly Blog EMAIL

Email

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---