Custom Function to count background color

Last week one of my colleague asked me how to count the cells in the excel sheet with specific color. Well there is no such in build function to do this job. So lets write our function. Yes we can write a custom function, also known as User Defined Function that will count the number of cells in a specified range for the specified background color.

1. This function takes two arguments, first argument is the range of cells like A1:A2 and the second argument is cell that has the background color that you want to count.

2. How to use this function. Enter this in the cell where you want the count to be displayed.

=count_background_color(A2:A16,A5)

A2:A16 is the Range of Cells

A5 is the cell with the background color that you want to count.

Here is the function code.

Function count_background_color(input_range As Range, cell_has_color As Range)
 
Application.Volatile
'Automatically update the formula whenever any cell is updated
' First parameter: Range of Cells
' Second parameter: Cell that has background color
 
Dim item As Range 'input_range is the first parameter
'Dim cell_has_color As Range 'Second parameter
 
Dim color_number As Variant
 
color_number = cell_has_color.Interior.Color
'Extract the color index of the second parameter
 
For Each item In input_range
    If item.Interior.Color = color_number Then
        count_background_color = count_background_color + 1
    End If
 
Next
 
End Function

Hope it helps you. You can write similar functions to make your life easy.