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):
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…
COUNTBLANK function is used to get the total count of Blank or Empty cell in range.
COUNTBLANK Function has one required argument i.e. range.
When creating a formula in Excel, many people get confused by the $ symbol in cell references. But itβs actually very simple. The dollar sign in an Excel cell reference tells Excel whether to keep the reference the same or allow it to change when you copy the formula to other cells. This guide will explain how it works and why itβs helpful.
Understanding Excel cell references is really important. Knowing the difference between absolute, relative, and mixed references will help you get the most out of Excel formulas and functions.
This article will show you how to create a timeline for Excel PivotTables and charts, making it easy to explore your data in a fun and interactive way. Imagine you have your data neatly arranged…
Introduction Calculate Percentage in Excel :-The term “per cent” comes from the Latin per centum, meaning “by the hundred.” A percentage is a way to show a part of something out of 100. You can…
Video: How to Hide Worksheet in Excel? Hide Sheet in Excel When I was creating an excel dashboard, there were multiple sheets which I used for calculation purpose and never wanted anybody to make any…
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 π