Count Colored Cells in Excel

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

Top 3 Methods to Count Colored Cells In Excel

There is no built-in function to count colored cells in Excel, but below mentioned are three different methods to do this task.

  1. Count colored cells by using the Auto Filter option
  2. Count colored cells by using the VBA code
  3. Count colored cells by using the FIND method
Table of contents

Now, let us discuss each of them in detail –

#1 - Excel Count Colored Cells By Using Auto Filter Option

For this example, let us look at the below data.

Count colored cells excel example 1

As we can see, each city is marked with different colors. So, we need to count the number of cities based on cell color.

As we can see, all the colors in the data. Now, we must choose the color that we want to filter.

We must follow the below steps to count cells by color.

  1. We must first apply the filter to the data.


    Count colored cells excel example 1-1

  2. At the bottom of the data, we need to apply the SUBTOTAL function in Excel to count cells.


    Count colored cells excel example 1-2

  3. The SUBTOTAL function contains many formulas. It is helpful if we want to count, sum, and average only visible cell data. Under the heading PIN, we must click on the drop-down list filter and select Choose by Color.


    Count colored cells excel example 1-3

  4. As we can see, all the colors in the data. Now, we must choose the color that we want to filter.


    Count colored cells excel example 1-4
    Wow!!! As we can see in cell D21, our SUBTOTAL function is given the count of filtered cells as 6 instead of the previous result of 18.

Similarly, now we must choose other colors to get the count of the same.

example 1-5

So, blue-colored cells count to five now.

#2 - Excel Count Colored Cells by using VBA Code

VBA's street smart techniques help us reduce time consumption at our workplace for some complicated issues.

We can reduce time, but we can also create our functions to fit our needs. For example, we can create a function to count cells based on color in one such function. Below is the VBA code to create a function to count cells based on color.

Code:

Function Color_Cell_Count(ColorCell As Range, DataRange As Range)

Dim Data_Range As Range
Dim Cell_Color As Long

Cell_Color = ColorCell.Interior.ColorIndex

For Each Data_Range In DataRange

If Data_Range.Interior.ColorIndex = Cell_Color Then
Color_Cell_Count = Color_Cell_Count + 1
End If

Next Data_Range

End Function

Then, copy and paste the above code to your module.

Count Cells using VBA Code 1

This code is not a SUB Procedure to run. Rather, it is a “User Defined Function” (UDF).

The first line of the code “Color_Cell_Count” is the function name. Now, we must create three cells and color them as below.

Count Cells using VBA Code 1-1

Now, we must open the function “Color_Cell_Count” in the G2 cell.

Count Cells using VBA Code 1-2

Even though we do not see the syntax of this function, the first argument is what color we need to count, so we must select cell F2.

Count Cells using VBA Code 1-3

The second argument is to select the range of cells as D2:D19.

Count Cells using VBA Code 1-4

Now, close the bracket and press the "Enter” key. As a result, it will provide the count of cells with the selected cell color.

Count Cells using VBA Code 1-5

Like this, with the help of UDF in VBA, we can count cells based on cell color.

#3 - Excel Count Colored Cells by Using FIND Method

We can also count cells based on the FIND method as well.

  • Step 1: First, we must select the range of cells where we need to count cells.
Count Cells by Use FIND Method 1
  • Step 2: Now, we need to press Ctrl + F to open the FIND dialog box.
Count Cells by Use FIND Method 1-1
  • Step 3: Now, click on “Options>>.”
Count Cells by Use FIND Method 1-2
  • Step 4: Consequently, it will expand the "Find” dialog box. Now, we must click on the “Format” option.
Count Cells by Use FIND Method 1-3
  • Step 5: Now, it will open up the “Find Format” dialog box. We need to click on the “Choose Format From Cell” option.
Using FIND Method 1-4
  • Step 6: Now, move the mouse pointer to see the pointer to select the format cell in excel that we are looking to count.
Using FIND Method 1-5
  • Step 7: We will select the cell formatted as the desired cell count. We have chosen the F2 cell as the desired cell format, and now we can see the preview.
Using FIND Method 1-6
  • Step 8: Now, click on the "Find All" option to get the count of the selected cell format count of cells.
Using FIND Method 1-7

So, a total of 6 cells were found with selected formatting colors.

Things to Remember

  • The provided VBA code is not a Subprocedure in VBA; it is a UDF.
  • The SUBTOTAL contains many formulas used to get the result only for visible cells when the filter is applied.
  • We do not have any built-in function in Excel to count cells based on the color of the cell.