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):
The only thing worse than wrong data in your spreadsheet is Empty Cells. If these blanks aren’t filled, they can cause problems when using formulas. I usually fill these empty cells with 0 or “NA”…
Microsoft Excel helps you organize and work with data in many ways. When you have a lot of information or need to break it into parts, splitting cells is very useful. Splitting cells means taking the content of one cell and dividing it into multiple cells. This can be especially helpful for separating names, addresses, or other data that are combined in one cell. Learn how to split cells in Excel using its built-in functions and formulas.
The tutorial explains how to use the CHOOSE function in Excel, showing you the basics and some interesting examples. While CHOOSE might seem simple on its own, when you combine it with other functions, it can be powerful. Essentially, the CHOOSE function helps you pick a value from a list based on its position. The tutorial also covers some advanced ways to use CHOOSE that you might find very useful.
Lock Cells to avoid editing, Hide Formulas Sometimes you create amazing projects, dashboards however people can make mistakes and edit the calculations, formats what you have made. Still you can protect the sheets, workbooks and…
Print Row Header on Each Page in Excel means that when you print your worksheet, the selected row (often the top row with column headers) will repeat at the top of every printed page. This ensures…
In this post, you’ll learn how to Show formulas in Excel spreadsheets. Usually, when you create a formula in Excel, it shows the result in the cell. But sometimes, you might want to see the…
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 🙂