Count Colored Cells (simple VBA)

Yesterday, I was working on tutorial part and I came across the biggest challenge. Do you want to know what it is? Worry not; I am there to share my thoughts with you. The challenge here is, in a set of data ( for eg: B2:B100) few cells are highlighted with some color, say Green, now, How to count the number of cells highlighted in Green?

Any guess??

Any guesses???

Ok, I will give you an example.

For example, In Excel version 2007, we have total of 347 functions. In this, 05 are newly added Functions compared to previous (2003) version. These 5 functions are highlighted in a list and now how to count those five functions using Excel functions???.

I tried and tried using different functions and finally my brain cells got activated, then I thought, Why can't I do using VBA? Yes, this can be done using VBA.

...

VBA CODE TO COUNT COLORED CELLS:

To make every one understand, I have created a simple VBA UDF function to do this job for me as well as for you.

Don't panic seeing the code because even I was scared some time before. But, now I am used it. ;)

//Code Start

Function CountColoredCells(Rng As Range, ColorRange As Range) As Long Dim Cell As Range Dim Counter As Long Dim ColorIndex As Integer ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex Counter = 0 For Each Cell In Rng.Cells If Cell.Interior.ColorIndex = ColorIndex Then Counter = Counter + 1 End If Next Cell Set Cell = Nothing CountColoredCells = CounterEnd Function

//Code End

CRACK THE CODE:

Let me tell you what each line of code does for us from behind the screen to achieve the results.

Line 1:Function CountColoredCells(Rng As Range, ColorRange As Range) As Long

Giving Name to the UDF function and passing values to execute the results in coding language

Line 1 (a): CountColoredCells is the new function adding to our Excel after executing this code like SUM, IF, VLOOKUP etc.,

Dim Cell As RangeDim Counter As LongDim ColorIndex As Integer

Initially we need to declare few keywords that needs to use while writing the program / code. Here I have used 3 keywords, i.e., Cell, Counter, ColorIndex.

Line 5:

ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex

This code will check one cell at a time in a range whether the cell is filled with color or not

ColorIndex = ColorRange.Cells(1, 1).font.ColorIndex

This code will check one cell at a time in a range whether the font is applied to the cell content or not

This line of code is not added in the main code, can add if required.

Line 6:

Counter = 0

Here counter will start with 0 to count the color in the cell.

Line 7:

For Each Cell In Rng.Cells

This code will work like an array function

Line 8:

If Cell.Interior.ColorIndex = ColorIndex Then

The If condition checks if the first cell is colored or not

If Cell.Font.ColorIndex = ColorIndex Then

Similarly to count the cell font use this code which is not their in the code list. Add if you require...

Line 9:

Counter = Counter + 1

If colored, counter will start as 1 else 0, that means counter = 1+1 or 0+1

Line 10:

End IfNext Cell

This will end the IF condition and moves to the next cell to check if it is colored.

Line 12:

Set Cell = Nothing

Initially, Cell should be set as nothing which is zero

Line 13:

CountColoredCells = Counter

This will start the counter and then moves inside the loop or an array

Line 14:

End Function

That's it. This will end the complete function.

HOW TO USE User Defined Function?

Once the function is created using VBA code also known as User Defined Function, start using similar to other functions in Excel.

Before going to the function, In this function we have two arguments.

1. Range of cells

2. Match Color

Like this,

=CountColoredCells("Range of Cells","Matching Color")

Type this Function in the cell and select the range of cells (column B) where to identify or to count the colored cells (like B23) and which color is referring to count (D19).

Have you used this function or similar functions in the work life. Then, please do share your experience with the blog members to enhance their skills. Use this space to comment, to discuss, to experience, to share and to conclude your thoughts.

If you like this post, share it, like it and post it on your social networking walls using the icons placed towards the right hand side top corner or using left hand side icons. Want to like this page then use the icons placed in the bottom of the right hand side page.