exceltoxl.com

Hi there,

   Share on Facebook

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 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 = Counter
End 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 Range
Dim Counter As Long
Dim 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 If
Next 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).

That's it. Magic works!!! :)

DOWNLOAD working file.

DO YOU USE THIS FUNCTION?

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.

Subscribe here to receive latest posts through an e-mail.

Thanks for your support once again.

SOME IMPORTANT LINKS:

LIST FILE NAMES IN A FOLDER (no Vba)

COUNT WORKSHEETS IN A WORKBOOK (no vba)

CONDITIONAL FORMATTING - IN FINGER TIPS

HOW HUMAN PARTS RESEMBLES EXCEL FEATURES

Thanks,