Count cells based on background color programmatically
This article demonstrates a VBA macro that counts cells based on their background color.
What's on this page
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.
2. VBA code
The following macro lets you count background colors, however, note that it won't count cells colored with conditional formatting.
'Name macro Sub CountColors() 'This macro counts background colors in cell range 'https://www.get-digital-help.com/2017/03/30/count-colored-cells/ 'Dimension variables and declare data types Dim IntColors() As Long, i As Integer Dim chk As Boolean 'Ask user for a cell range and save the output to range variable rng Set rng = Application.InputBox("Select a cell range to count colors: ", , , , , , , 8) 'Redimension array variable IntColors ReDim IntColors(0 To 2, 0) 'For Each ... Next statement 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
3. 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.
Note, save your workbook with file extension *.xlsm (macro-enabled workbook) to keep the code attached to your workbook.
4. Instructions
- Press Alt + F8 to open the macro dialog box, it shows a list of macros currently in your open workbooks.
- Press with mouse on macro CountColors with left mouse button to slect it.
- Press the "Run" button on the dialog box.
- 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 the 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 cell's property one by one. I don't recommend using this with larger cell ranges unless you are prepared to wait for a while.
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]
The array formula in cell F3 counts cells in column B that contains at least one of the values in […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
Question: I have a question that I can’t seem to find an answer to: I want to make a full […]
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]
Question: Sam asks: Is there a formula that can count blocks For eg in your picture (see picture above) if […]
Joe asks: I have a worksheet that has rows, each containing sequential groupings of values of "1" and "0". These […]
This article describes a formula that counts values in two columns if they are duplicates on the same row. What's […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
The formula in cell D18 counts unique distinct months in cell range B3:B16. Formula in D18: =SUMPRODUCT((FREQUENCY(DATE(YEAR($B$3:$B$16), MONTH($B$3:$B$16), 1), DATE(YEAR($B$3:$B$16), […]
The image above demonstrates three different formulas in column E that counts unique numbers unique distinct numbers duplicate numbers from […]
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
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 press with left mouse […]
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]
This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]
In this article I will show you how to extract the most frequent value (text or number) between two dates […]
In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]
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
Paste image link to your comment.