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.
- 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.
- 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
I hope you enjoyed this blog.