## Count colored cells

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 'http://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?

- Select and copy code above (Ctrl+c)
- Open VB Editor (Alt+F11)
- Insert a new module to your workbook
- Paste code to code module

### Instructions

- Press Alt + F8 to see a list of macros
- Select CountColors
- Press "Run" button
- 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.

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Count cells between a given value

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]

Count unique distinct values in two columns

Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double click on cell C12 […]

Count records between two dates with multiple parameters

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

Count unique distinct values in a filtered table

A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a […]

Count unique distinct values within same week, month or year

Introduction What are unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count […]

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

Count overlapping days in multiple date ranges, part 2

In the previous post I explained how to count overlapping dates between a single date range and multiple date ranges. In […]

Count unique distinct records with a date and column criteria

davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form