How to Count Colored Cells in Excel

Excel offers conditional formatting to apply logic against the data and extract the information based on the logic applied. Yet, Conditional formatting in excel has limits. That’s when Excel VBA comes to the rescue. Today, we will use Excel VBA and understand how to count colored cells in excel.

Become a Data Visualization Pro

Tableau Desktop Specialist Certification TrainingExplore Program

How to Count Colored Cells in Excel?

Conditional formatting in excel has some limits; when you want to extend a little, Excel VBA is for the rescue. Today we will consider a sample dataset as shown below and color individual cells differently. 

Later, we will apply excel VBA Code to identify individual colored cells and count them. Let’s begin.

Initially, the developer options on Microsoft Excel will be disabled. Let’s enable it through the steps as mentioned below.

Steps to Enable Developer Options on Microsoft Excel:

• Step 1: Click on the “File” menu

• Step 2: Navigate to the “Options” button.

Become an AI-powered Business Analyst

With Purdue University's Business Analysis ProgramExplore Now

• Step 3: Navigate to the “Customize Ribbon” option.
• Step 4: Enable the “Developer” options, as shown in the image below.

Now, the developer options are available on the toolbar. Click on the “Developer” options on the toolbar and you can see the following options as shown below.

Now, click on the “Macros” option to design a Macro for your customized function.

A new dialogue box will be displayed on your screen where you can write the name for your Customized Macro and create it using the create button.

Now, we will be creating a new macro named “CountColoredCells.” This will take you to a new coding window in Excel VBA as shown below.

The following code can be used to identify the colored cells in excel”

Function CountColoredCells(CurrentCell As Range, SpreadSheetArea As Range) As Long

    Dim ColoredCell As Range

    Dim colorCode As Long

    Dim ColoredCellCount As Long

    colorCode = CurrentCell.Interior.color

    For Each ColoredCell In SpreadSheetArea

        If ColoredCell.Interior.color = colorCode Then

            ColoredCell.Interior.color = ColoredCellCount + 1

        End If

    Next ColoredCell

    CountColoredCells = ColoredCellCount

End Function

The code uses the “ColoredCell.Interior.color” function to recognize the coiled filled in the cells selected. The variable “ColoredCell.Interior.color” is used to count the number of unique colors in excel and display the result.

Become an AI-powered Business Analyst

With Purdue University's Business Analysis ProgramExplore Now

Now, let’s call the new custom macro as shown in the image below.

The parameters are explained below:

• CountColoredCell - Macro.
• H2 is the cell location of the color to be identified.
• C2:C31 is the range of cells to consider to identify the colored cells.

Now the same cell can be dragged across all the cells to identify their respective colored cells. The final result will be displayed as shown below.

With this, we have reached the end of this article on “How to Count Colored Cells in Excel.” If you have any queries about the concepts covered in this article, do let us know in the comments section below.

Kick-start your career growth story with our Professional Certificate Program in Business Analysis. Get a chance to master Excel, Tableau, and Python tools. Start learning now!

Become a Data Visualization Pro

Tableau Desktop Specialist Certification TrainingExplore Program

Next Steps

How to Group Rows in Excel can be your next milestone in mastering excel. The excel dashboards might have to make the user-interface simple. The filters and slicers may complicate things for the end user. Including Groups in Excel could be simple and helpful in reducing complexity. 

Keen to learn more about Business Analytics with excel? Simplilearn's Professional Certificate Program in Business Analysis course could be of great help. It is considered the best among the industry's top business analytics courses. This Simplilearn program is a result-oriented training and certification program that helps you master statistics and data analytics concepts. 

Should you have questions about this tutorial on "How to Count Colored Cells in Excel"? Please feel free to write to us in the comments below. Our expert team will resolve them and will be happy to answer them at the earliest.