Thread: Compare Cell.ID to Cell.Value not working for numbers

I have the following function 'Trigger' in Excel. It watches a range A1:A10 for any changes to the cell values so =Trigger(A1:A10).

Works great if the cell values are text, ie. YES or NO, if I make a change to one cell in this range it only displays the msgbox with that particular cell address which is what I want.

BUT... I need the range to be numbers, i.e. 10.47. If I make the range numbers and change one cell value the function displays a msgbox for each cell address in the range even though only one cell value has changed. Please help! Appreciation in advance.

Function Trigger(ByVal rEntireRange As Range) As Date
    For Each rCell In rEntireRange
        If (Not IsEmpty(rCell)) And (Not IsError(rCell)) And (rCell.ID <> rCell.Value) Then
            MsgBox rCell.Address & " is changed"
            rCell.ID = rCell.Value
        End If
    Next rCell
    Trigger = Now
End Function