How do I count colored cells in Excel using a macro?

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

How do I count colored cells in Excel using a macro?

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.

VBA Code to Count Cells by Color

Lets have a look at the first code: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

Below is the second code to count cells by color: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 Note It is worth to mention that both above codes do not count the cells where colors are coming through conditional formatting.

How can I use this VBA code?

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

VBA Code to Count Cells by Color

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)

VBA Code to Count Cells by Color

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)

VBA Code to Count Cells by Color

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

VBA Code to Count Cells by Color

Step 2: Right click and select Format Cells

VBA Code to Count Cells by Color

Step 3: In the Format Cells dialog box go to Fill tab and click on More Colors

VBA Code to Count Cells by Color

Step 4: Thats all, in the Color dialog box, you can view the RGB (Red, Green, Blue) codes of the color

VBA Code to Count Cells by Color

Thanks for reading the article, subscribe us to get more VBA tricks

Video liên quan