I need to know how to count cells in a column, that have a certain color pattern.
Hi, Pls try this.Code
Sub Test() 'ColorIdx of RED is 3 MsgBox CountByColor([A1:A100], 3) End Sub Function CountByColor(ByVal rng As Range, ByVal ColorIdx As Long) As Long Dim lngCnt As Long, c As Range For Each c In rng If c.Interior.ColorIndex = ColorIdx Then lngCnt = lngCnt + 1 Next CountByColor = lngCnt End Function
Hi Colo, good one.
I have different approach.
Say if you want to have a summary of colored cells in the worksheets!!!
Then both the inputs should be ranges.
Public Function Cnt_Color(ByVal which_color As Range, ByVal rng As Range) As Long
Dim lngCnt As Long, c As Range
For Each c In rng
If c.Interior.ColorIndex = which_color.Interior.ColorIndex _
Then lngCnt = lngCnt + 1
Cnt_Color = lngCnt
Also attached a sheet!!!
Note: Once you have pasted a function in a module as Public, it can be accessed from Fx which will also give you what parameters to pass.
Attached sheet, somehow the sheet did not get attached last time. :puzzled:
Roy & Dave,
I was surprised to find that i was exactly on the same lines, an without reading it
Thanks for the help!