Cell values not refreshing for custom function

[ClovisBelley]

I have defined a function that calculates the sum of cells depending on their color. This function works perfectly but refuses to refresh, it doesn't refresh when I click calculate sheet, it doesn't refresh when I calculate cell. The only way is to select it, edit the formula then click enter. I'd like it to refresh automatically, here is the code for the function.

Function SumColoredCells(CC As Range, RR As Range) As Double
    Dim X As Double
    Dim Y As Long
    Y = CC.Interior.ColorIndex
    For Each I In RR
        If I.Interior.ColorIndex = Y Then
            X = X + I.Value
        End If
    Next I
    SumColoredCells = X
End Function

[#2 · Accepted solution]

@ClovisBelley 

I want refresh calculation in this VBA code.

you can try adding the following line of code at the beginning of your function:

Application.Volatile

This will make sure that Excel recalculates the function every time there is a change in the worksheet. If this does not work, you can try adding a dummy argument to your function and changing its value every time you want to recalculate the function.

Here’s an example:

   Application.Volatile
    Dim X As Double
    Dim Y As Long
    Y = CC.Interior.ColorIndex
    For Each I In RR
        If I.Interior.ColorIndex = Y Then
            X = X + I.Value
        End If
    Next I
    SumColoredCells = X
End Function```

Hope I could help you with this information.

NikolinoDE

I know I don't know anything (Socrates)

[#3]

Thank you, this works great but seems to really slow things down, every time I change a value it takes 4 seconds for everything to start working again.