custom function recalculation

[kendo679]

I'm using a custom function but it causes the entire sheet to recalculate no matter what cells I enter data to. Seems like it should only recalc when altering a cell that contains the custom formula.

 

[MrTeeny]

Maybe your custom function is turning the sheet to auto calculate, hard to know without seeing the function. Excel is supposed to be quite efficient as to what and when it recalculates a sheet

 

[kendo679]

Thanks for your response,

The sheet is set in the options page to auto calculate, but I still don't understand why it does so no matter what cell I alter.
here is the code:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)


Application.Volatile
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
If lCol = -4142 Then
vResult = 0
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

 

[kendo679]

I even commented out the "Application.Volatile" and it still recalculates every time I change anything on the sheet
I inserted a msgbox to prove that the function is running. There are dozens of cells with the custom formula so I had to hold the enter key down for 5 seconds while it ran through them all.