How to Count Colored Cells in Excel?

Written by Last updated November 1, 2026

📋 FREE EXCEL TIPS EBOOK - Click here to get your copy

Wouldn’t it be great if there was a function that could count colored cells in Excel?

Sadly, there isn’t any inbuilt function to do this.

BUT..

It can easily be done.

In this tutorial, I will show you four ways to count colored cells in Excel (with and without VBA):

Using Filter and SUBTOTAL function
Using Find All (no formula needed)
Using COUNTIF and the GET.CELL function
Using a Custom Function created using VBA

If your cells are colored by conditional formatting rather than by hand, I cover that at the end, because it needs a different approach.

Follow along with the example file

Count Colored Cells in Excel.xlsm

This Article Covers

Count Colored Cells Using Filter and SUBTOTAL

To count colored cells in Excel, you need to use the following two steps:

Filter colored cells
Use the SUBTOTAL function to count colored cells that are visible (after filtering).

Suppose you have a sales dataset as shown below, and you want to count the green and orange rows in cell B22.

Sales dataset with green and orange rows, a Visible Rows label in A22 and an empty B22

There are two background colors used in this data set (green and orange).

Here are the steps to count colored cells in Excel:

In any cell below the data set, use the following formula (I’m using B22). Leave at least one blank row above it, so the filter doesn’t hide your formula.

=SUBTOTAL(103,B2:B20)

SUBTOTAL formula in B22 returning 19 on the unfiltered data

Select the headers.

Headers selected in A1:E1

Go to Data –> Sort & Filter –> Filter. This will apply a filter to all the headers.

Filter in the Sort & Filter group on the Data tab

Click on any of the filter drop-downs. The colors here fill entire rows, so any column works. If your color is only in one column, use that column’s drop-down.

Filter arrows on all the headers, with the Region drop-down open

Go to ‘Filter by Color’ and select the color. In the above dataset, since there are two colors used for highlighting the cells, the filter shows two colors to filter these cells.

Filter by Color flyout showing the two colors, with the green swatch marked

As soon as you filter the cells, you will notice that the value in the SUBTOTAL function changes and returns only the number of cells that are visible after filtering. Filtering by green gives you 3, and filtering by orange gives you 4.

Only the three green rows visible after filtering, with SUBTOTAL in B22 returning 3

How does this work?

The SUBTOTAL function uses 103 as the first argument, which is used to count visible cells (hidden rows are not counted) in the specified range.

If the data is not filtered it returns 19, but if it is filtered, then it only returns the count of the visible cells.

Note that 103 counts every cell that isn’t empty, so use a column that has a value in every row. If you use 102 instead, it only counts numbers, which means it returns 0 on a text column like Region.

Pro Tip: On Windows, the keyboard shortcut for a filter is Ctrl+Shift+L. On Mac, use Cmd+Shift+F (Ctrl+Shift+L also works).

Also read: How to Count Filtered Rows in Excel?

Count Colored Cells Without a Formula (Find All)

If you just need to see the count once, you can get it from the Find and Replace dialog box. You don’t need a formula or a filter.

Below I have the same sales dataset, and I want to see how many cells in column A have the green color.

Sales dataset with green and orange rows

Here are the steps to do this on Windows:

Press Ctrl+F to open Find and Replace, then click Options.

Find and Replace dialog on the Find tab with Options expanded

Click the small arrow on the Format button and select “Choose Format From Cell”.

Format drop-down in Find and Replace with Choose Format From Cell marked

Click any green cell, such as A6. The Preview box next to the Format button now shows the green fill.

Preview box in Find and Replace showing the green fill picked from cell A6

Picking the color also selects the cell you clicked, which is why you select the range next.

Select A2:A20. The dialog box stays open while you do this.

A2:A20 selected with the Find and Replace dialog still open

When more than one cell is selected, Find searches only that selection, so colored cells elsewhere on the sheet stay out of your count.

Click Find All.

Find All results listing A6, A12 and A19, with 3 cell(s) found at the bottom

The bottom of the dialog box now shows 3 cell(s) found, and that’s your count.

Choose Format From Cell copies all of that cell’s formatting (like its number format and font), and not just the fill. So a green cell that’s formatted differently won’t be counted.

If your colored cells have mixed formatting, click the arrow on the Format button and select Clear Find Format. Then click the Format button itself, and pick the color on the Fill tab.

This count doesn’t stay anywhere in your worksheet, so you need to repeat the steps when the colors change. If you want a count that sits in a cell, use the next two methods.

Follow along with the example file

Count Colored Cells in Excel.xlsm

Count Colored Cells Using COUNTIF and GET.CELL

If you’ve tried COUNTIF on its own, you’ve seen that it can’t do this, because its criteria only look at what’s in a cell and not at its color.

So this method first gets a number for each color, and then uses COUNTIF to count those numbers.

GET.CELL is a Macro4 function that has been kept due to compatibility reasons. It does not work if used as regular functions in the worksheet.

However, it works in Excel named ranges.

See Also: Know more about GET.CELL function.

Below I have the same sales dataset, and I want a color code for each row in column F and the count of each color in cells B23 and B24.

Sales dataset with an empty Color Code column in F, and Green and Orange cells in A23:A24 with empty counts

Important: GET.CELL is an Excel 4.0 macro function, and Microsoft 365 blocks these by default. If it’s blocked, the color codes below show #BLOCKED! and the count shows 19 (all the rows), which is wrong but looks like a real answer.

To enable them, go to File > Options > Trust Center > Trust Center Settings > Macro Settings. Check “Enable Excel 4.0 macros when VBA macros are enabled”, and then click Enable Content when you open the file.

If you downloaded the example file and Excel says it has blocked the macros, close it, right-click the file, and select Properties. Check Unblock on the General tab, click OK, and open the file again (the VBA method below needs this too).

Here are the three steps to use GET.CELL to count colored cells in Excel:

Create a Named Range using GET.CELL function
Use the Named Range to get color code in a column
Using the Color Number to Count the number of Colored Cells (by color)

Let’s deep dive and see what to do in each of the three mentioned steps.

Creating a Named Range

Select cell F2 (you’ll see why this matters in a moment).

Cell F2 selected

Go to Formulas –> Define Name.

Define Name in the Defined Names group on the Formulas tab

In the New Name dialog box, enter GetColor as the Name, keep Workbook as the Scope, and enter the formula below in the Refers to field.

=GET.CELL(38,'Color Codes'!$A2)

New Name dialog with GetColor as the Name, Workbook as the Scope and the GET.CELL formula in Refers to

In the above formula, I have used ‘Color Codes’!$A2 as the second argument. You need to use the reference of the column where you have the cells with the background color.

Color Codes is the name of the sheet in the example file, so use your own sheet’s name in your workbook. The 38 tells GET.CELL that we want the cell’s color.

Because F2 was selected when you created the name, $A2 means column A in the same row as the formula. So GetColor in F2 reads A2, and GetColor in F6 reads A6.

If a different row is selected when you create the name, every code would read the wrong row.

Click OK.

Getting the Color Code for Each Cell

In the cell adjacent to the data (F2 here), use the following formula, and then copy it down to F20:

=GetColor

=GetColor in F2 copied down to F20, returning 36 for green, 46 for orange and 0 for no fill

The name returns one color code at a time, so each row needs its own formula, which is why you copy it down. Pointing the name at all of A2:A20 to save the copying doesn’t work, because you only get one value back.

This formula would return 0 if there is NO background color in a cell and would return a specific number if there is a background color.

This number is specific to a color, so all the cells with the same background color get the same number. Here, green is 36 and orange is 46.

The number is the nearest of Excel’s 56 basic palette colors, so two very similar shades can get the same number and be counted together. If that happens, use more distinct colors or the VBA method below, which matches the exact color.

Count Colored Cells using the Color Code

If you follow the above process, you would have a column with numbers corresponding to the background color in it.

To get the count of a specific color:

Somewhere below the dataset, give the same background color to a cell that you want to count. Make sure you are doing this in the same column that you used in creating the named range. For example, I used Column A, and hence I will use the cells in column ‘A’ only (A23 is green and A24 is orange).
In the adjacent cell, use the following formula, and then copy it to B24:

=COUNTIF($F$2:$F$20,GetColor)

COUNTIF with GetColor in B23, returning 3 for green and 4 for orange

This formula will give you the count of all the cells with the specified background color. B23 returns 3 for green, and B24 returns 4 for orange.

How Does It Work?

The COUNTIF function uses the named range (GetColor) as the criteria. The named range in the formula refers to the adjacent cell on the left (in column A) and returns the color code for that cell.

Hence, this color code number is the criteria.

The COUNTIF function uses the range ($F$2:$F$20) which holds the color code numbers of all the cells and returns the count based on the criteria number.

So the count depends on the fill in A23 and A24. To count a different color, change the fill in that cell.

Changing a cell’s color doesn’t update the codes or the count, and pressing F9 doesn’t refresh them either. Press Ctrl+Alt+F9 to recalculate everything after you change or remove a color.

If you save the file as .xlsx, the GET.CELL name stops working. Save it as a macro-enabled workbook (.xlsm) to keep it.

Follow along with the example file

Count Colored Cells in Excel.xlsm

Count Colored Cells Using VBA (GetColorCount)

In the above methods, you learned how to count colored cells without using VBA.

But, if you are fine with using VBA, this is the easiest of the three formula methods.

Using VBA, we would create a custom function, that would work like a COUNTIF function and return the count of cells with the specific background color.

Here is the code:

'Code created by Sumit Bansal from https://trumpexcel.com
Function GetColorCount(CountRange As Range, CountColor As Range) As Long
Dim rCell As Range
Dim CountColorValue As Long
Dim TotalCount As Long
Application.Volatile
CountColorValue = CountColor.Interior.Color
For Each rCell In CountRange
  If rCell.Interior.Color = CountColorValue Then
    TotalCount = TotalCount + 1
  End If
Next rCell
GetColorCount = TotalCount
End Function

'Code created by Sumit Bansal from https://trumpexcel.com
Function GetColorCount(CountRange As Range, CountColor As Range) As Long
Dim rCell As Range
Dim CountColorValue As Long
Dim TotalCount As Long
Application.Volatile
CountColorValue = CountColor.Interior.Color
For Each rCell In CountRange
  If rCell.Interior.Color = CountColorValue Then
    TotalCount = TotalCount + 1
  End If
Next rCell
GetColorCount = TotalCount
End Function

The code checks the exact fill color of each cell, so two similar shades are counted separately.

To create this custom function:

With your workbook active, press Alt + F11 (or right click on the worksheet tab and select View Code). This would open the VB Editor.

VB Editor open with the workbook's sheets in the Project pane on the left

In the left pane, under the workbook in which you are working, right-click on any of the worksheets and select Insert –> Module. This would insert a new module.

Right-click menu on a worksheet in the Project pane, with Insert and Module marked

Double click on the module name (by default the name of the module is Module1) and paste the code in the code window.

GetColorCount function code pasted into the module window

Close the VB Editor.

That’s it! You now have a custom function in the worksheet called GetColorCount.

To use this function, simply use it as any regular excel function.

Syntax: =GetColorCount(CountRange, CountColor)

CountRange: the range in which you want to count the cells with the specified background color.
CountColor: the color for which you want to count the cells.

Below I have the same sales dataset, and I want the count of green cells in G2 and the count of orange cells in G3.

Sales dataset with a Color Count column in G, where G2 is green and G3 is orange, and both are empty

To use this formula, use the same background color (that you want to count) in a cell and use the formula. CountColor argument would be the same cell where you are entering the formula.

So G2 has the green fill, and here is the formula to enter in it:

=GetColorCount($A$2:$A$20,G2)

GetColorCount in G2 returning 3 for green

G2 returns 3. You don’t get a circular reference warning even though the formula refers to its own cell, because the function only reads the cell’s color and not its value.

G3 has the orange fill, so it gets its own formula. Don’t copy G2 down with the fill handle, because that also copies the green fill and you’d get the green count again.

=GetColorCount($A$2:$A$20,G3)

GetColorCount in G3 returning 4 for orange

G3 returns 4.

A color change on its own doesn’t make Excel recalculate. So after you apply, change, or remove a color, press F9 to update the count (the Application.Volatile line in the code is what lets F9 update it).

Use a specific range like $A$2:$A$20 rather than a whole column. The function checks cells one by one on every recalculation, so pointing it at all of column A can take several seconds each time.

Note: Since there is a code in the workbook, save it with a .xls or .xlsm extension.

VBA doesn’t run in Excel for the web, so you need the desktop version of Excel for this method.

Pro Tip: If you count by color often, keep the code in your Personal Macro Workbook so it’s available in every workbook. A function stored there is called with the file name in front, like =PERSONAL.XLSB!GetColorCount($A$2:$A$20,G2).

Count Cells Colored by Conditional Formatting

The Find All, GET.CELL, and VBA methods only see colors you applied yourself. They don’t see colors from conditional formatting, so the GET.CELL code for such a cell is still 0.

In this case, you don’t really need the color at all. The rule already knows which cells are colored, so you can count with the same condition the rule uses.

Below I have the same sales dataset, where a conditional formatting rule colors the quantities above 75 in green, and I want the count of those green cells in cell B22.

Sales dataset with quantities above 75 in green from a conditional formatting rule, a label in A22 and an empty B22

Here is the formula that will give us the count:

=COUNTIF(C2:C20,">75")

COUNTIF in B22 returning 6, matching the six green cells in column C

B22 returns 6, which matches the six green cells in column C.

The formula and the rule are separate, so if you change the 75 in the rule, you need to change it in the formula too. If your rule checks more than one condition, use COUNTIFS with those same conditions.

If you don’t know what the rule is, you can use the first method in this tutorial, with the filter drop-down on the colored column (Quantity here).

Filter by Color also lists colors from conditional formatting, and the SUBTOTAL formula counts the rows that are left.

So these are four ways to count colored cells in Excel, plus the formula to use when the colors come from conditional formatting. I hope you found this Excel tutorial useful.

Do you know any other way to count colored cells in Excel?

If yes, do share it with me by leaving a comment.

You May Also Like the Following Excel Tutorials:

Sumit Bansal

13x Microsoft Excel MVP

Hey! I'm Sumit Bansal, founder of trumpexcel.com and a Microsoft Excel MVP. I started this site in 2013 because I genuinely love Microsoft Excel (yes, really!) and wanted to share that passion through easy Excel tutorials, tips, and Excel training videos. My goal is straightforward: help you master Excel skills so you can work smarter, boost productivity, and maybe even enjoy spreadsheets along the way!

LinkedIn YouTube X (Twitter)

68 thoughts on “How to Count Colored Cells in Excel?”

MJ

January 2025 at 5:27 am

Thank you for this!
Reply
Peter T Kupfer

October 2020 at 10:55 pm

I’m noticing that the VBA method doesn’t work with conditional formatting of background colors. Any workaround for hat?
Reply
- James Langran
  
  October 2020 at 1:40 pm
  
  I have the same issue
  Reply
Guest

October 2020 at 9:04 am

Hi, I am using the VBA module to count preferred meeting session times, it was working OK – until I added a separate VBA module called CounxlDiagonalDown to count diagonal borders (people who have indicated they are not attending said meeting), and now I am getting a #NAME? syntax error on the GetColorCount. any suggestions on a fix? Thanks
Reply
Erik Steiner

September 2020 at 10:47 am

Thanks, your VB macro works perfectly!!
Reply
Jeffrey Fry

August 2020 at 4:32 am

Great stuff, but the VB does not work on conditional formatting.. only if you manually change the background color of cells. Have any idea to count backgrounds with conditional formating?
Reply
Kristi

August 2020 at 9:01 pm

Thank you so much. Just what I needed.
Reply
Carlito

August 2020 at 11:29 pm

Great vba function! Very useful. I change the ‘interior’ to ‘font’ and it works nicely when counting range with different font colors. However, I tried it to conditional formatting and it didn’t work. Any idea, why?
Reply
karan kankariya

August 2020 at 1:37 pm

Does this work with horizontal range. Doesnt seem to be working in mine with an horizontal range
Reply
Rachel

July 2020 at 11:36 pm

Fantastic video and article. Very helpful and simple to follow. Thank you!
Reply
Carlos

July 2020 at 7:22 pm

Simple and straight forward. This did the job. Thank you!
Reply
Sunil Pinto

June 2020 at 2:41 pm

I have a set of two ranges with compassion:
Say my A2:A10 with data of
B2:B10 with data of
I applied function in C2 and draged upto C10<=if(isna(Match($A$2:$A$10,$B$2:$B$10,0)),A2," ")
Which give me the result as (this result says not shared number of Set A2:A10 to B2:B10)
Now I coloured Full set of A2:A10 and also B4 and B9 as yellow and I need the out put in C2 to C10 as

Can any body help me to fix this problem
Reply
guest555

May 2020 at 1:58 am

(VBA Solution)
Too good to be true…just throws the standard formula error (“not trying to type a formula etc.”
Reply
Nally Linh

May 2020 at 2:31 pm

Thank you so much, this solve my problems.
Reply
A coder

May 2020 at 8:51 am

The code in the screen shot of the VBA has errors
Reply
Natasha Stephens

April 2020 at 6:46 pm

Help! Is there anyway to modify this VBA so that it works for =max
Reply
BANWARI

March 2020 at 1:14 pm

GOOD TRICKS FOR COUNTING BY COLOUR
Reply
justin carr

February 2020 at 1:48 pm

tried =SUBTOTAL(102,$E$2:$E$20) and didn’t work. waste of time
Reply
Strann0

December 2019 at 3:58 am

Is there a way to count the cells by color but also having a criteria?. For example I want to count the cells in a column that are colored grey but only the ones that have an specific value on them like having the word “unique” in the cell.

I would really appreciate the help thanks
Reply
- aaa
  
  January 2020 at 7:47 pm
  
  I know this is kind of a ghetto solution, but:
  I just copied the unique cells I needed to a different worksheet and did the function there, it worked perfectly
  Reply
Jay

November 2019 at 9:34 pm

Great code. However, it does not automatically update, unless I click on the cell each time. Please note, I have excel set to automatically update calculations, so not sure why not working.
Reply
Jemmy

November 2019 at 4:56 pm

The VBA function worked great!! I’m now showing off to anyone in the office who will listen! Thanks very much
Reply
Sheila Easley

September 2019 at 7:57 pm

Hello! I cannot get my get.cell (38,…) to work. Is there any chance I could contact you?
Reply
- Paulo
  
  November 2019 at 6:12 pm
  
  Can you please try with ; instead of , ? It work for me.
  Reply
Lt. TMAC

September 2019 at 7:34 pm

Using VBA was perfect! Thanks
Reply
Luna

July 2019 at 1:16 am

I’m using your VBA method, and it works just fine, but is there any way to make it work for cells that have RGB color values instead of the standard ones from the color palette? Using .ColorIndex only works for those types of colors
Reply
Olaf

June 2019 at 10:48 am

Hi,
The function option works fine, there is only one thing. As soon as one of the colours changes the sum won’t refresh, is there an way to sort this out ?
Reply
- Daniel Boey
  
  August 2019 at 2:12 pm
  
  Delete the module > save > close and reopen file > add the module again > click on any cell with the formula > press “enter”
  Reply
- Guest
  
  February 2020 at 2:56 pm
  
  Push F2.
  Push Enter.
  Reply
Sarah

April 2019 at 9:45 am

Is there a way to apply this to rows rather than columns?!
Reply
Jana

March 2019 at 3:02 am

The VBA formula worked great on my spreadsheet. But, I saved it and re-opened and now all the cells where I had formulas say #Name?. I checked and the code is still in the module. It comes up in the list when I begin to type =getcolorcount. I tried removing the Code and re-inserting and can’t get the formula to work any longer. I’m using Excel 2010, is there any reason this suddenly wouldn’t work now?

Please help – this formula was perfect for my spreadsheet for work because I didn’t want to have to add another column to the spreadsheet to get this to work.

Thanks,
Jana
Reply
Rida Fatima

March 2019 at 4:23 pm

Does this work in an online excel sheet?
Reply
Kiran

January 2019 at 1:03 am

Hi,
Thanks for the VBA code, I used your code and it works perfect – it counts the colored cells, but it does not update automatically. If a new cell is colored, everyctime I have to click on the formula to have it update the count.
Is there any other way to get this automated when a new cell is colored?
Appreciate your response
Reply
Derek

November 2018 at 8:18 pm

I’ve used your VB method and it works great, thank you so much! I do have an issue though that I’m having troubles resolving…

I’m using your formula as part of a calendar to track equipment utilization by filling the cells with color when the equipment is used. The problem I’m having is that the formula does not automatically recalculate when new colored cells are entered. It does recalculate when you click on the cell containing the formula and press enter, but I’ve got hundreds of assets that I’m tracking and that’s not an efficient way of doing it.

I’ve tried all the usual, F9, recalculate formula, recalculate worksheet, etc. nothing works. I’ve even recorded Macros of actually highlighting all formula cells and clicking enter. It works when I do it, but the macro returns a value error when used.

Do you have a work around for this or another VB Macro that can be assigned to a radio button to recalculate the colored cell totals each time the calendar is updated?
Reply
- GL
  
  January 2019 at 2:44 am
  
  Ctrl-Alt-F9
  Reply
RN

September 2018 at 12:24 am

I found this article very useful, yet the VBA functions didn’t work. I have a table with various data, I used the conditional formatting in one column and built 5 rules and based on that cells have been colored in greed, red and yellow…any thoughts?
Reply
- Guest
  
  February 2020 at 2:57 pm
  
  I have come across 5 functions that count coloured cells such as this code, and none of them work on conditional formatting.
  Reply
Greg

September 2018 at 2:27 am

I have used your following code and it works perfectly:

Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
If rCell.Interior.ColorIndex = CountColorValue Then
TotalCount = TotalCount + 1
End If
Next rCell
GetColorCount = TotalCount
End Function

But, now I want to do a little more. For the range of cells, I want to have 4 separate functions. 1) Count cells IF they are a particular color, as well as ending in “*o” 2) Count cells IF they are a particular color, as well as ending in “*s” 3) Count cells IF they are a particular color, as well as having 2 text characters in the cell, “??” and 4) Count cells IF they are a particular color, as well as having a number greater than zero in the cell, “>0”. How can I modify the base “GetColorCount” code to incorporate this additional parameter for each instance?
Reply
Trent

September 2018 at 1:25 am

I tried using your 3rd option but I am thinking that I am not right in doing so. Here is what I have and what I am trying to do. I have a spreadsheet with my supervisors and their clock-in times that I export from our schedule and then copy & paste into workbook. I color each row based on if they were on time, late but the time rolled back (7 minute grace period) and late but jumped forward 1/4 hour. I then filter by the supervisor’s last name and then by the “ErrorLog” header, checking only CLOCK IN & LATE CLOCK IN. The “Clock In” option could be either green (on time) or orange (late but rolled back) and then Late Clock In is yellow. The entire ErrorLog column is K10:K118, for all supervisors. Obviously when I sort by both Last Name and ErrorLog headers it reduces the number of rows and hides all the rest. I just want the formula to count each color that is visible. Is what I am doing even possible? I want to be able to change up the filter a bit by changing the Last Name so that I can only see each supervisor individually.
Reply
Robert Ross

January 2018 at 8:31 pm

Great video…. I do have a question for you… I used your code and it works perfect except for one thing, it counts the colored cells, but it does not update automatically. If a new cell is colored, I have to click on the formula to have it update the count. Is there a code I can add to have it update automatically once a new colored cell is added? Thanks again for a great video!!!
Reply
GaryMak

December 2017 at 3:57 am

Your VBA solution works BUT NOT with colors from “Conditional Formating.”

I have 17 cells in a column, all under a conditional formatting to turn the cell color “light red” if a certain condition is met. There are only 3 cells that are “light red” (meeting the condition) but your VBA script returns an answer of “17”, meaning it considers all cells “light red”. Then I manually went in and colored one cell (not already highlighted by the conditional formatting) blue, and your VBA returned an answer of “16”. Clearly then, it does not recognize the results of conditional formatting, only “manually entered” colors.

Any solution? This is critical as the colored cells will be different depending on what conditions are met. I need a way to count them per each condition.

(I learned a lot about adding a custom VBA code. Thanks!)
Reply
- Martina
  
  October 2019 at 7:06 pm
  
  Same issue! Any suggestions?
  Reply
- Carlito
  
  August 2020 at 11:34 pm
  
  Was there any answer to this question? thanks.
  Reply
Fiona Graham

September 2017 at 7:49 pm

Hi,
can you use both GetColorCount and Sumbycolor VBA in the same worksheet.
Reply
Aamir Naqshbandi

June 2017 at 1:42 pm

Can you please explain what is 38 mentioned in the name manager formula? What does it relate to?
Reply
- Peter
  
  January 2020 at 11:31 pm
  
  If you follow the link to more information about Get.Cell it tells you, I was wondering the same thing.
  Reply
Emilian

March 2017 at 10:23 pm

When i press ‘enter’ with you’r download exel i get #NAME? why? PS ; I am new in VBA
Reply
Godmot Vlogs Sagsinthecity

October 2016 at 9:14 am

https://uploads.disquscdn.com/images/3f93ede176e443ec907f29b2d6cd8c3b4960f606edfc43f4bb412848e5fe379a.png https://uploads.disquscdn.com/images/d440cd03823e1b89b27883aa9710447c85aa338bfdeb78c6c36c755916928480.png Hello, I am trouble getting the VBE working for counting coloured cells, I have tried all the suggestions but still not getting it. The closest I got to was that it appeared “0” which did not calculate the coloured cell. That I guess is an improvement from #NAME?” or #VALUE?” messages. Please help!!
Reply
- Godmot Vlogs Sagsinthecity
  
  October 2016 at 9:16 am
  
  Please email me at sagsinthecity@gmail.com or message me at https://www.facebook.com/SAGSOFFICIAL Thanks
  Reply
mart

July 2016 at 1:25 pm

Hi, please assist. if the cell is in cf it doesn’t count. i am using also the standard cf for date occuring.
Reply
- Sumit Bansal
  
  August 2016 at 11:03 am
  
  Hey Mart.. Yes this doesn’t work when conditional formatting is in play. IF you want to count based on CF rules, you can use the same rule you have used to apply conditional formatting and count the total values. For example, if you use CF to highlight cells that contain “Yes”, then use a COUNTIF function to count all these cells.
  Reply
Ryan

May 2016 at 10:21 pm

How can you accomplish the same thing except counting cells in a row versus column (as outlined here)? For example, I want to know how many of each color (Red, Yellow, Green, and no color) are in row 2 (range – A2:I2).
Side note/issue: The Subtotal function seems to only count cells that have something in them. I would like to count cells that are just a color without any text in the cell. Is that possible?
Thanks for your help.
Reply
- Zita
  
  April 2019 at 2:50 pm
  
  I am looking for this answer as well. I need the count of cells from a bigger range a2:bz52. And some cells are merged. Is there a way for this? Thank you in advance! Zita
  Reply
Azz

February 2016 at 1:53 am

I liked the VBA option, works like a charm. However if my data range is non-continuous , for example, instead of A1:A10, I need to count the cell color in A1, A4, A9, How should I modify the formula? Thanks.
Reply
- Dal
  
  August 2016 at 12:30 pm
  
  Hi Azz,
  Enclose your non-continuous range within brackets inside the function, (x, y, z, a:c), so for your example, you would use:
  
  =GetColorCount( ( A1, A4, A9 ), 50 )
  
  Where,
  50 = Green box color index.
  Reply
audeser

November 2015 at 8:42 pm

If declared strictly and with names more appealing (IMO ;)). Thank you for the code and the idea behind

Public Function GetColorCount(ByRef Target As Excel.Range, ByRef rgColor As Excel.Range) As Long
Dim rCell As Excel.Range
Dim Color As Long
Dim lgCounter As Long

Color = rgColor.Interior.ColorIndex
For Each rCell In Target
If rCell.Interior.ColorIndex = Color Then lgCounter = lgCounter + 1
Next rCell
GetColorCount = lgCounter
End Function
Reply
Nico Do

August 2015 at 4:00 pm

Hi,

I started using your Count Cells Based on Background Color in Excel #3 using VBA. This wors very good. Thanks for it.
But now I have a problem using the same function by checking cells where the background color is set by Conditional Formatting. I am checking for dates older than today() and mark these cells in red background; works well using conditional formatting but the count doesn’t work for these cells.
thank you for any idea or hint
Reply
- Raymond
  
  August 2015 at 11:15 pm
  
  Hi Nico,
  
  Do you use a formula in your conditional formatting (CF)?
  If you do, it returns TRUE and you are coloring the cells that meet that condition. This means that if you put the CF formula into a COUNTIF formula, you can count the cells that meet the CF conditions!
  
  Gr,
  Raymond.
  Reply
  - Nico Do
    
    August 2015 at 11:22 am
    
    Hi Raymond,
    
    No I’m not using a formula. I use a standard rule type: Cell Value “less than or equal to” =$A$1
    In A+ I only update the day =today().
    thx
    Nico
    Reply
    - Raymond
      
      August 2015 at 4:07 pm
      
      Nico,
      
      I did a test and I realised I needed a help column to do the job.
      
      Say that range A2:A10 has your values where you apply the CF to.
      Formula in help column B:
      in B2 put ‘=OR(A2<$A$1,A2=$A$1)', copy down and you will get a couple of TRUEs or FALSEs.
      
      The count formula anywhere can then be: =COUNTIF(B2:B10,TRUE).
      
      Or you can just use an IF formula in the help column, giving you 1 or 0 back. Then a simple SUM somewhere and there you are.
      
      I must be do-able without the help column but I don't know how (yet)!
      
      Raymond
      Reply
      - Nico Do
        
        September 2015 at 12:38 pm
        
        Hi Raymond,
        
        A help column isn’t very usefull and would need many additional help columns, so I searched and searched.
        On http://www.excel-inside.de (a german Excel & VBA site) I found an example using .Font.ColorIndex . With this information I found .Interior.ColorIndex and my day was made 🙂
        
        Next step: searching for the color indexes on https://msdn.microsoft.com/en-us/library/office/ff840443.aspx
        
        and it seems to work – refreshing manually after changes – but it works:
        ‘Function ColorRed(Area As Range)
        ‘ColorRed = 0
        ‘For Each cell In Area
        ‘If cell.Interior.ColorIndex = 3 Then
        ‘ColorRed = ColorRed + cell.Count
        ‘End If
        ‘Next
        ‘End Function
        Nico
      - Raymond
        
        September 2015 at 6:19 pm
        
        Hi Nico,
        
        Thanks for the update and also for you providing the resources that led to the solution you found!
        
        An UDF (User Defined Function) is harmless in your case because you are using it in only one cell to get the count. So that’s great.
        In complex workbooks, where formulas must be in several rows, UDF’s are not recommanded unless no other choice because they make the workbook slow.
        And as they say, there is always another way, a simple, faster and stronger way…
        
        I found a non-vba solution: an array formula.
        
        Put the following formula adjusted to your needs where you want your count.
        In this example, I assume the IF statement is the same as in your conditional format that gives the cells a red color when TRUE. The formula says:
        for each cell in range A2:A10, if the cell value is equal or less than the value of A1, give 1, otherwise give 0. Then give a sum of all the individual results.
        I’ve used 1 IF with an OR but it didn’t work so it became 2 IF’s.
        
        Instead of just pressing ENTER, press CTRL+SHIFT+ENTER to make it an array formula, which you can recognize by the { } that Excel automatically puts around it:
        =SUM(IF(A2:A10<$A$1,1,IF(A2:A10=$A$1,1,0)))
        
        So after CTRL+SHIFT+ENTER, you will see:
        {=SUM(IF(A2:A10<$A$1,1,IF(A2:A10=$A$1,1,0)))}
        
        No more manual refresh!
        
        More about the topic:
        http://www.cpearson.com/excel/arrayformulas.aspx
        
        Gr,
        Raymond.
      - cezi
        
        January 2016 at 2:27 pm
        
        i am not a vbe user but this explanation was clear enough to take the dive;
        so i tried solution number 3 and it works!
        however, how can i make the sheet update the numbers after i changed the color of a cell? at this moment i need to go to this cell en hit enter;
        there must be a ‘trick’ to do this on my imac!
      - Raymond
        
        January 2016 at 2:59 am
        
        Hi Cezi,
        
        The only workaround I could came up with involves 2 steps:
        [1] Put ‘Application.Volatile True’ at the beginning of the the function code
        [2] Put the following code in the worksheet event area (it assumes that you want an update if you select any cell in range [A1:A10]; please adjust accordingly):
        
        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        ‘Application.StatusBar = False
        On Error Resume Next ‘To avoid error if the selection isn’t in [A1:A10]
        If Target.Address = Intersect(Target, Range(“A1:A10”)).Address Then
        ActiveSheet.Calculate
        ‘Application.StatusBar = “Calculated”
        End If
        End Sub
        
        Gr,
        Raymond.
      - Kara
        
        March 2016 at 4:46 pm
        
        Hello Raymond, I am having the same issue but am not exactly sure how to follow the workaround you posted.
        
        [1] Should I paste ‘Application.Volatile True’ after the following row?
        
        ‘Function GetColorCount(CountRange As Range, CountColor As Range)’
        
        [2] Where should I put the code you have entered? I’m afraid I don’t know what the “worksheet event area” is or where I can find it.
        
        Thank you for your patience and a great guide!
        
        /Kara
      - Raymond
        
        March 2016 at 9:54 pm
        
        Hello Kara,
        
        [1] Correct.
        [2] While in Excel, press ‘ALT+F11’ and that will bring you in the development environment.
        Then, follow the instructions I’ve put here (screenshot):
        
        http://www.ehbeo.rayorganization.com/vba/screenshots/worksheets/Events_SelectionChange.png
        
        Gr,
        Raymond.
        http://www.ehbeo.com
      - Amy
        
        June 2016 at 2:20 am
        
        Raymond,
        
        Your code for the workaround however disables copying and pasting? Do you know how to fix this?
        
        -Amy
      - Raymond
        
        June 2016 at 7:38 pm
        
        Hi Amy,
        
        Sorry about that. Yes, there is a fix. The first line of code should be:
        If Application.CutCopyMode Then Exit Sub
        
        So, the final worksheet ‘SelectionChange’ event should look like this:
        [CODE]
        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        ‘If we are copying, do nothing and exit
        If Application.CutCopyMode Then Exit Sub
        
        On Error Resume Next ‘To avoid error if the selection isn’t in [A1:A10]
        If Target.Address = Intersect(Target, Range(“A1:A10”)).Address Then
        ActiveSheet.Calculate
        End If
        End Sub
        [END CODE]
        
        -Raymond

Leave a Comment Cancel reply