As Microsoft is releasing new versions of Excel after every few years, they are working on adding more and more features in the application to support increased user demands. There are hundreds of formulas/functions available in Excel which can be used to get any type of information from the data.
Have you ever felt that Microsoft should have added a formula in Excel which can count the cells based on specific color? I have seen many code requests to share a VBA code that can count the cells by its color.
To help our subscribers and developers, we are sharing 2 codes that be used to count the cells with specific color and returns the count of the matching color cells.
Public Function CountByColor(rng As Range, Red As Long, Green As Long, Blue As Long) As Long
'Variable declaration
Dim lCount As Long
Dim rngCell As Range
'Loop throught each cell in the range
For Each rngCell In rng
'Checking and count color
If rngCell.Interior.Color = RGB(Red, Green, Blue) Then
lCount = lCount + 1
End If
Next
'Return the value
CountByColor = lCount
End Function
Public Function CountByColor(rng As Range, ColorCell As Range) As Double
'Variable declaration
Dim lCount As Long
Dim rngCell As Range
'Loop throught each cell in the range
For Each rngCell In rng
'Checking and count color
If rngCell.Interior.Color = ColorCell.Interior.Color Then
lCount = lCount + 1
End If
Next
'Return the value
CountByColor = lCount
End Function
If you want to use this code in your VBA tool, then follow below steps:
Step 1: Open the Excel file in which you want to copy this code
Step 2: Press Alt+F11 to open VBA editor
Step 3: Insert a new module from Insert > Module menu
Step 4: Paste the code in the module
Step 5: Now you are ready to use this code as Excel Function/Formula
Step 6: Type the formula in the cell where you want to get the count of specific colored cells
First Code Example:=CountByColor(A2:A11,146, 208, 80)
The first parameter of the formula is the range from which you want to count the specific color cells. The second, third and fourth parameters are RGB code of the color.
Second Code Example:=CountByColor(A2:A11,A8)
The first parameter of the formula is the range from which you want to count the specific color cells. The second parameter is the cell from which you want to compare the color.
In the first VBA code, we have used RGB color codes to find the matching cells and sum the values. You can find RGB codes of any color using following steps:
Step 1: Select the cell which contain the color you want to use
Step 2: Right click and select ‘Format Cells…’
Step 3: In the Format Cells dialog box go to ‘Fill’ tab and click on ‘More Colors…’
Step 4: That’s all, in the ‘Color’ dialog box, you can view the RGB (Red, Green, Blue) codes of the color
Thanks for reading the article, subscribe us to get more VBA tricks
We are offering Excel VBA Course for Beginners to Experts at discounted prices. The courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion
This course is going to help you to excel your skills in Excel VBA with our real time case studies.
Lets get connected and start learning now. Click here to Enroll.
Here is another best rated Excel Charts and Graph Course from ExcelSirJi. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.
This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.
So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.
Hope you are enjoying learning Excel with us, if you want any support related to this article, please do comment else you can ask questions in Excel Community
VBA code that will sum cells by its color through excel function. This code will really help in making the analysis and presentation better.
Did you come across any requirement where you want the user to interact with a sheet only through VBA Form? Here is a simple code which can help you.
VBA CODE TO HIDE MENU RIBBON IN MS ACCESS In MS Access, there are multiple ways to protect your code or tool from un-authorized access and edits. Some developers prefers to hide MS Access ribbons…
VBA Code to Sort Data HERE IS A ONE LINE CODE FOR DEVELOPER’S REFERENCE WHICH CAN BE USED TO SORT DATA ‘Sort data in ascending order on Column F (Created At) Sheet1.Range(“A1:G” & Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row).Sort Key1:=Sheet1.Range(“F1”),…
Excel Function- WORKDAY.INTL WORKDAY.INTL function is an advanced version of WORKDAY function with additional advantage of “Custom weekend options” For Example, with WORKDAY function weekends are treated as “Saturday and Sundays” however if you need…
How to Add Outlook Reference in Excel VBA? To automate Outlook based tasks from Excel you need to add Outlook Object Library (Microsoft Outlook XX.X Object Library) in Excel References. You can follow below steps…
This helped heaps thank you very much! I now I am wondering how do I write up a VBA code to auto-update or simultaneously count cells by color when I am doing color coding my cells. Cheers
Place the code in Worksheet change event (the sheet) where you want to use this.