FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

# Count Colored Cells in Excel

Updated on December 27, 2023
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

## 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

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.

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.

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

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.

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

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.

So, blue-colored cells count to five now.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

### #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 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.

This code is not a SUB Procedure to run. Rather, it is a “” (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.

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

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.

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

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

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.
• Step 2: Now, we need to press Ctrl + F to open the FIND dialog box.
• Step 3: Now, click on “Options>>.”
• Step 4: Consequently, it will expand the “Find” dialog box. Now, we must click on the “Format” option.
• Step 5: Now, it will open up the “Find Format” dialog box. We need to click on the “Choose Format From Cell” option.
• Step 6: Now, move the mouse pointer to see the pointer to select the  that we are looking to count.
• 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.
• Step 8: Now, click on the “Find All” option to get the count of the selected cell format count of cells.

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

### Things to Remember

• The provided VBA code is not a ; 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.

This article has been a guide to Count Colored Cells in Excel. We learned to count colored cells using the auto filter option, VBA code, FIND method, and downloadable Excel template. You may learn more about Excel from the following articles: –