I need to know how to count cells in a column, that have a certain color pattern.
Counting cells of different color
-
-
-
Hi, Pls try this.
Code
Display MoreSub 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 LongDim lngCnt As Long, c As Range
For Each c In rng
If c.Interior.ColorIndex = which_color.Interior.ColorIndex _
Then lngCnt = lngCnt + 1
NextCnt_Color = lngCnt
End Function
===========================================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:
-
Check out Chip Pearson's article on this subject :
-
-
No need to leave OzGrid :bsmile: See: http://www.ozgrid.com/VBA/Sum.htm There is one to count by color and sum by color. This one: http://www.ozgrid.com/VBA/Sort.htm will even Sort by color.
-
Roy & Dave,
Good Links!!
I was surprised to find that i was exactly on the same lines, an without reading it
-
Hey Everybody,
Thanks for the help!
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!