Count by color using VBA in Microsoft Excel
 

I am having the following formula to count on the basis of colour. For example
Total 12 cell out of which Red - 1, Yellow - 5 & Green - 6 so by using this
formula this should give proper count as mentioned above.

I need you assistance as i have never used macro using VBA in Microsoft
Excel.

Could you please guide me (Step by Step) how to run this macro in excel.

Appreciate your kind assistance.

Thanks in advance.
Kamlesh

Function CountByColor(InputRange As Range, ColorRange as Range) As Long
Dim cl As Range, TempCount As Long, ColorIndex As Integer
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempCount = 0
For Each cl In InputRange.Cells
If cl.Interior.ColorIndex = ColorIndex Then
TempCount = TempCount + 1
End If
Next cl
Set cl = Nothing
CountByColor = TempCount
End Function

Count by color using VBA in Microsoft Excel
 

Hi Kam

Copy the code to the clipboard.
In Excel press Alt+F11.
This will open the Visual Basic Editor (VBE).
From the menus select InsertModule.
Paste the code into the new module (major white part on right of screen).
Click on the Excel icon (Left most button on toolbar) to return to Excel.
In the cell that you want your result type the formula:
=countbycolor(D4:D16,D4)
Where D4:D16 is the range of cells you want to check and D4 is a single
cell which is set to the colour you are wanting to count. This does not
need to be in the first range.

Hope this helps
Rowan

Kam wrote:
I am having the following formula to count on the basis of colour. For example
Total 12 cell out of which Red - 1, Yellow - 5 & Green - 6 so by using this
formula this should give proper count as mentioned above.

I need you assistance as i have never used macro using VBA in Microsoft
Excel.

Could you please guide me (Step by Step) how to run this macro in excel.

Appreciate your kind assistance.

Thanks in advance.
Kamlesh

Function CountByColor(InputRange As Range, ColorRange as Range) As Long
Dim cl As Range, TempCount As Long, ColorIndex As Integer
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempCount = 0
For Each cl In InputRange.Cells
If cl.Interior.ColorIndex = ColorIndex Then
TempCount = TempCount + 1
End If
Next cl
Set cl = Nothing
CountByColor = TempCount
End Function