Application Volatile Not Working?

[atbarrajas]

Hi everyone,

So I wrote a short function to sum the values in a defined range only if the text is not black, but the formula is not automatically calculating. I share this sheet with others who won't be willing to force a calculation so it's imperative that it works with as little interference from the user as possible. I've checked my calculation settings, added Application.Volatile and it still isn't working. Any help you all could provide would be greatly appreciated.

Thank you.

Function SUMGREEN(rng As Range)    
    Application.Volatile

    Dim cell As Range, x As Integer
    
    For Each cell In rng
    
        If cell.Font.Color <> vbBlack Then
                SUMGREEN = SUMGREEN + cell.Value
        End If
    
    Next cell
    
End Function

 

[shg]

Changing a cell's color doesn't trigger calculation. You and your users will need to press F9 to see the result.

Color is not data to Excel.

 

[Jonmo1]

Adding .Volatile makes the code run anytime a calcluation is triggered.

The problem is that changing the format of a cell doesn't trigger a Calculation.

 

[atbarrajas]

Ah, I see. I thought that it would be able to use formatting as a trigger. Thank you for your help.