Counting cell with different colors
Posted by Jack on September 09, 1999 3:45 PM
I am trying to come up with a way to have excel count the cells that are color coded. Does anyone know how to do this?.
Thanks
Posted by Ivan Moala on September 10, 1999 3:43 AM
Jack here is one solution, there are a number
of ways to do it, and probably better
Function CountColorCode(Range, CCode)
Set YourDataRange = Intersect(Range.Parent.UsedRange, Range)
kount = 0
For Each cell In YourDataRange
If cell.Interior.ColorIndex = CCode Then kount = kount + 1
Next cell
CountColorCode = kount
End Function
The only draw back on this is it will not give
you the correct answer if the cell is conditionally
formated. I'll look at another method latter.
regards
Ivan
Posted by Ivan Moala on September 10, 1999 3:48 AM
Sorry jack forgot to comment it (was in a rush to
answer Q from another group)
This is a User defined function that uses
your selected "range" and Color code "CCode"
For the codes of the colors see Online Help re: color codes.
Paste this function into a module via
Alt F11, select VBA project, insert Module.
Jack here is one solution, there are a number
of ways to do it, and probably better
Function CountColorCode(Range, CCode)
Set YourDataRange = Intersect(Range.Parent.UsedRange, Range)
kount = 0
For Each cell In YourDataRange
If cell.Interior.ColorIndex = CCode Then kount = kount + 1
Next cell
CountColorCode = kount
End Function
The only draw back on this is it will not give
you the correct answer if the cell is conditionally
formated. I'll look at another method latter.
regards
Ivan
