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:
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
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 !