Recalculate formula when format changes

[Closed]
Report
Posts
1
Registration date
Friday November 30, 2012
Status
Member
Last seen
November 30, 2012
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I have a VB module that sums cells with a specific color background. It works fine but I have to click on the cell with the formula to get the module to recalculate the sum whenever the color of a cell in the defined range changes. How can I get this to run automatically? Here's the VB module:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell) + vResult
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

Thanks!

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Format does not trigger any event. So it depends how your are changing color. If you are changing color via some code, then you can embed the call to recalc. Other wise your best option, IMHO, would be to write a macro that does recalcuations are you want and run it when even you want or at a given interval