Recalculate formula when format changes

Closed
simpsoj40 Posts 1 Registration date Friday November 30, 2012 Status Member Last seen November 30, 2012 - Nov 30, 2012 at 09:55 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Dec 1, 2012 at 07:29 AM
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!

Related:

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 1, 2012 at 07:29 AM
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
0