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):
VBA Code to Count Color Cells With Conditional Formatting Have you ever got into situation in office where you need to count the cells with specific color in conditional formatted Excel sheet? If yes then…
WEEKDAY function applies to a Date and returns the output for Day of the week. The output of the function varies from 0 to 7
INT function is used to round down the numeric value to nearest integer. INT Function has one required argument i.e. number.
AVERAGE function is used to get the average of numbers. Function applies formula i.e. average = Sum of all values / (Divided by) number of items.
SUMIF function is used to get the “total sum” for number of times the criteria across range is met. SUMIF Function has two required arguments.
Few Excel Tips 1. CHANGE DIRECTION WHEN YOU PRESS ENTER Whenever you press enter, you must be thinking why my cell selection shifts down. Why it can’t go UP, Down, Left. Surprised This is very…
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!