VBA code, count using color

This is really funny thing in MS Excel I discovered today. Yes, a VBA code to count cells using colors. The intention behind is this post is to share VBA code and help you move ahead with VBA learning. This function is written by me. As you go below you will find formula syntax, vba code and add-ins. So, you can use add-ins on your system without replicating code and you can use this add-in all in open MS Excel file.

Syntax:  =CountColor(TargetRange,CellwithColor)

TargetRange: Range of cells which you wanted to count under which you want to count cells using criteria as another formatted cell.

CellwithColor: Single cell which filled with color which you want to count from TargetRange.

Note: It wont count cells that are formatted conditionally.

It may sound complex, but trust its very simple to use. Take a look at screen shot below where I have illustrated using CountColor.

 

Now, lets take a look a code of function which calculate count based on colors.

Public Function CountColor(target As Range, clr As Range) As Integer

Dim cnt As Integer

cnt = 0

For Each c In target
        If c.Interior.ColorIndex = clr.Interior.ColorIndex Then
            cnt = cnt + 1
        End If
Next c

CountColor = cnt

End Function

Dim cnt as Integer -> declare the integer variable used to count cells.

For each c in Target - > each c refers to one cell in target range while looping through for loop. So, if the target range is A1:A10 then while looping its goes A1, A2, A3.... ,A10.

c.Interior.colorIndex -> Colorindex returns the number for color, Interior of cell refers to color pattern and c is cell from target range. Similarly, clr.Interior.ColorIndex returns the number for color pattern in criteria.  So, if the number returned by getcolorindex method matches with number returned by getcolorindex of criteria it adds 1 to cnt.

After completing the entire range it pass the value to Countcolor.

If you still have doubts, download the count color example file. I am sure you would love to use this function.

Download Count color example

Macro are good to use when you want to process or perform operation in one MS Excel file. However, when you want to use any function/vba code independently in all open MS Excel file than add-ins are the best option. Other good thing about add-ins is they don't prompt whether you want to enable or disable code. Hence, I have uploaded the add-ins for you to use and send it across to your friends. Please feel free to download, its Free.

 Download Countcolor Add-ins

If you need help with installing add-ins, please visit our post How to install Add-ins.

Thanks for spending time in reading post. Request you to leave your comments. Also, you can receive updates, add-ins and e-books, its free.

We assure you knowledge, not SPAM!