In this post we will see how we could count the cells with the same background color e.g. number of blue background, number of red background in a particular range.
Below is an example in which we want to count the number of cells with the same background color and as you can see we have counted the number of background colors in the next column.
As you can see, we have three cells with background color green, 3 cells with background color yellow and one cell with background color red.
To count the number of cells with a particular background 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 background color.
Step 3
Now double click the newly created module to open that and paste the below code in the module.
Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Application.Volatile
cntRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByColor = cntRes
End Function
This excel function takes two argument, first argument is the range in which we want to count the cells with same background color and the second argument is the criteria for the background 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 background color.
Enter the function in cell B10 as shown below.
In the formula =CountColour($A$1:$A$7,A10), the range $A$1:$A$7 is the data range in which we count the number of cells with background color and the second argument is A1, which specifies the criteria for background color which is green in this case.
We have fixed the range A1:A7 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 green cells in the range is 3.
Step 5
Now drag the formula downwards to get the count of all the background colors available in the data range.
Hope this helped.
