Running Excel 2007
Windows Vista
The goal is to have a VBA user defined function recalculate when any cell color is changed.
So far, I have the "Automatic" selected under the "Calculation Options".
I have added "Application.Volatile" to my VBA code. I have also tried, CTRL+ALT+SHIFT+F9,
and this successfully causes the user defined function to recalculate.
Yet, I want the formula to be recalculated each time a cell color is changed. Thanks for the help.
___________________________
Here is my VBA code:
Function SumCell_Sel(rColor As Range, rRange As Range)
Application.Volatile
Dim rCell As Range
Dim iCol As Long
Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rRange
If rCell.Interior.ColorIndex = iCol Then
' if logic that ensures sum operation occurs with only numbers
If (WorksheetFunction.IsNumber(rCell)) Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
End If
Next rCell
SumCell_Sel = vResult
End Function
Windows Vista
The goal is to have a VBA user defined function recalculate when any cell color is changed.
So far, I have the "Automatic" selected under the "Calculation Options".
I have added "Application.Volatile" to my VBA code. I have also tried, CTRL+ALT+SHIFT+F9,
and this successfully causes the user defined function to recalculate.
Yet, I want the formula to be recalculated each time a cell color is changed. Thanks for the help.
___________________________
Here is my VBA code:
Function SumCell_Sel(rColor As Range, rRange As Range)
Application.Volatile
Dim rCell As Range
Dim iCol As Long
Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rRange
If rCell.Interior.ColorIndex = iCol Then
' if logic that ensures sum operation occurs with only numbers
If (WorksheetFunction.IsNumber(rCell)) Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
End If
Next rCell
SumCell_Sel = vResult
End Function