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.
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.
Step 2
Insert a module in which we will write a VB function to count the cells with same 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.
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.
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.
Step 5
Now drag the formula downwards to get the count of all the font colors available in the data range.
Hope this helped.
