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
Free File Renamer Tool – Quickly Rename files batch using Excel VBA Here is another help code and tool for programmers to rename files. You can use this tool for renaming all files available in…
Duplicate Files Finder Tool is an MS Excel based tool to identify duplicate files exist in your system or shared folder. The tool takes a base folder path as input and list down all duplicate files in the folder and sub-folders. It has capability to find duplicate files by comparing the names and their size.
In this tutorial, you’ll learn what an Excel array formula is, how to enter it properly, and how to use array constants and array functions.
Array formulas are a very powerful tool in Excel, allowing you to do multiple calculations with a single formula. One array formula can replace many regular formulas. However, most users—around 90%—have never used them because they seem intimidating to learn.
Array formulas are known to be one of the trickiest Excel features to understand. The goal of this tutorial is to make learning them as easy and simple as possible
Index Match in Excel are powerful tools for advanced lookups. They’re popular because they’re incredibly flexible. You can use them to search horizontally or vertically, perform two-way searches, find values to the left of your search criteria, handle case-sensitive searches, and even search using multiple conditions. If you want to improve your Excel skills, mastering INDEX and MATCH is essential. Below, you’ll find numerous examples to help you grasp how they function.
Here is one more wonderful free tool from ExcelSirJi.com which makes your life easy. Time & Motion Tracker helps you to track Start and End time of any type of transaction or activity. The good thing is, it is VBA based tool which helps you to protect from manual manipulation in the data by the user. It is also easy to use, just click on Start (shortcut: Ctrl+Shift+A) or Stop (Ctrl+Shft+S) buttons to record the time
Excel has built-in options to make graphs, it doesn’t have a heat map feature. However, you can quickly and easily create a heat map in Excel using conditional formatting.
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.