Have you ever got into situation in office where you need to count the cells in Excel sheet with specific color? If yes then you can use following code which counts the number of cells with specific color (here it is yellow) and put the count in cell
It is worth to mention that the above code does not count the cells where colors are coming through conditional formatting. You can use DisplayFormat.Interior.Color to get the conditional formatting colors (DisplayFormat.Interior.Color works only on Excel 2010 or above) or read thisย post
Public Function CountColorCells(rng As Range) As Long
'Variable declaration
Dim lColorCounter As Long
Dim rngCell As Range
'loop throught each cell in the range
For Each rngCell In rng
'Checking Yellor color
If Cells(rngCell.Row, rngCell.Column).Interior.Color = RGB(255, 255, 0) Then
lColorCounter = lColorCounter + 1
End If
Next
'Return the value
CountColorCells = lColorCounter
End Function
To use this code in your Excel file, follow below steps:
Here I have counted the cells with yellow color. You can change RGB (#,#,#) code to count other colors. You can find RGB codes of any color using following steps
Thatโs all, in the โColorโ dialog box, you can view the RGB (Red, Green, Blue) codes of the color
If you are look for a code to sum the cells based on itโs color then you can read thisย post.
In this tutorial, I will show you threeย ways to count colored cells in Excel (with and without VBA):
View two worksheets Side-by-Side in Excel lets you view multiple worksheets at once in layouts like vertical, horizontal, tiled, or cascade, so you donโt have to keep switching between sheets. You can also split a…
Scroll Lock in Excel can make scrolling, selecting cells, checking formulas, entering data, and recording macros harder. So, you might need to turn it off to work normally. In this article, weโll show you how…
How to Compare TWO Columns in Excel? Comparing Columns in Microsoft excel is much often feature that is used while managing database. We have many ways to compare the columns in excel and can be…
How to Insert Symbol in Excel? Have you ever faced the challenge of using special character symbols in number formatting or customized number formatting? It is easy to insert any symbol in numbers i.e Delta…
Conditional Formatting helps to easily identify the usual and unusual numbers/ conditions, creating interactive visualization by means of Highlight Cells by Rules, Top/Bottom Rules, Data Bars, Color Scales and Icon Sets.
Microsoft Excel lets you share your spreadsheets with others, but sometimes your co-workers might edit the wrong column by mistake. To avoid this, you can lock specific columns. In this guide, weโll show you how…
It doesn’t work with conditional formatting because the cell color does not really change
This code does not work with conditional formatting however we made it work. So please read below article to count colored cells with conditional formatting.
Count Colored Cells with Conditional Formatting
Hope you like the article. Please comment if you liked the solution. ๐
Happy Reading!
Great tips! I always struggled with counting colored cells in Excel, but your step-by-step guide made it so easy to follow. Thanks for sharing!
Thanks.
Great tips! I never knew counting colored cells could be so straightforward with Excel. Thanks for breaking it down step by step!
Thank you for the feedback. Keep learning ๐
Thank you for this detailed guide! Counting colored cells in Excel always seemed tricky to me, but your step-by-step instructions made it much easier to understand. I can’t wait to try it out in my own spreadsheets!
Thank you so much for your feedback. Keep learning ๐