I've been looking for a macro which can count specific colors in a range, making a calculation with this sum and putting the output in a specific cell. So far I've found this:
Code
Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long
Application.Volatile
cntRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent
CountCellsByColor = cntRes
End Function
Display More
But this function slows down my macro's a lot (and seems to slow even more over time). I thought that the Application.Volatile was slowing it down but removing it didn't solve it.
Does anyone have a good idea for a workaround that forces a recalculation but does not slow down the progress ?
For instance: Count all vbRed in Range C3:L44 and output in P2
And count all Interior.ColorIndex = 33 in Range C3:L44 and output in P5
I've also used this macro: http://www.ozgrid.com/VBA/Sum.htm but this also seems to slow down alot ?
Thanks in advance !
Maarten