« Create Symbols using Keyboard Shortcuts | Good Applause for Formulas Crash Course (Opens till Oct 13th) » |
Count Colored Cells (simple VBA)
Formulas, Learn Excel, VBAHi there,
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!!! :)
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,
You must be logged in to see the comments. Log in now!
If you have no account yet, you can register now! (It only takes a few seconds)
Recent comments