Count Cells Based On Font Color In Excel

In this post we will see how we could count the cells with the same font color e.g. number of blue font, number of red font in a particular range.

Below is an example in which we want to count the number of cells with the same font color and as you can see we have counted the number of font colors in the next column.

excel table showing count of cells with same font color

As you can see, we have three cells with font color blue, 2 cells with font color red and so on.




To count the number of cells with a particular font color in a range, we will write a small function in excel VBA as instructed below.

Step 1

Go to Developer tab and open VB editor in excel as shown below.

inserting VB editor to write function to count cells based on font color

Step 2

Insert a module in which we will write a VB function to count the cells with same font color.

inserting a module to write function to count cells based on font color

Step 3




Now double click the newly created module to open that and paste the below code in the module.

Public Function CountColour(pRange1 As Range, pRange2 As Range) As Double

Application.Volatile

Dim rng As Range

For Each rng In pRange1

    If rng.Font.Color = pRange2.Font.Color Then

        CountColour = CountColour + 1

    End If

Next

End Function

This excel function takes two argument, first argument is the range in which we want to count the cells with same font color and the second argument is the font color reference.

writing function in module to count cell based on font colors

write code to count cells with same font color

Step 4

Now save the file as macro enabled workbook (.xlsm) and now we can use the function that we have written to count the number of cells with a particular font color.

Enter the function in cell B2 as shown below.

enter the function in cell to count cell values based on font color

In the formula =CountColour($A$1:$A$6,A1), the range $A$1:$A$6 is the data range in which we count the number of cells with font color and the second argument is A1, which specifies the font color which is blue in this case.

We have fixed the range A1:A6 by pressing F4, so that it does not move when we drag the formula downwards.

Enter the formula and hit enter and you can see we have got the output as 3 as the number of blue font in the range is 3.

formula returns font with blue color

Step 5




Now drag the formula downwards to get the count of all the font colors available in the data range.

dragging excel formula to get all the font color count

Hope this helped.

Share The Knowledge

Random Posts

Leave a Reply