Ravi Sagar | Home | Blog

Mastering EXCEL VBA

Table of Contents

So this is it. My friend Vinod and I used to maintain a site called AutoGrid.info which we don't really maintain but every now and then we use Excel VBA for solving problems.

So I am thinking of consolidating everything here on this page. The plan is to have a big, very big page with plenty of content that you can use to solve your problems as well. You will find code here and of course the explanation.

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


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)

'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


End Function

I hope you enjoyed this blog.