My User-defined function doesn't update when recalculating

[ctrlaltdel]

I created the following public function to give us the color of cell shading that was used so that we could use it to get a count of how many times each shade was used:

Public Function Color(Optional rng As Range) As String
On Error GoTo NoRange
Color = rng.Item(1).Interior.ColorIndex
Exit Function
NoRange:
Color = "RANGE?"
End Function

However, when I change the color of a cell, the number doesn't automatically change. Recalculate doesn't work either. I am a super-novice here, so I sure hope one of you experts can help.

Thanks

 

[Andrew Poulsom]

Re: My User-defined function doesn't update when recalculati

Try:

Public Function Color(Optional rng As Range) As String
    Application.Volatile
    On Error GoTo NoRange
    Color = rng.Item(1).Interior.ColorIndex
    Exit Function
NoRange:
    Color = "RANGE?"
End Function

The function will recalculate when the workbook is calculated. But you will have to calculate manually if you just change the colour of the Range, because changing formatting does not trigger a calculation.

 

[ctrlaltdel]

Re: My User-defined function doesn't update when recalculati

Works great!

Thanks a bunch.