excel vba how to get the old value of a changed cell
'Place the following in the CODE MODULE of a WORKSHEET
'to track the last value for every cell in the used range:
Option Explicit
Private r As Range
Private Const d = "||"
Public Function ValueLast(r As Range)
On Error Resume Next
ValueLast = Split(r.ID, d)(1)
End Function
Private Sub Worksheet_Activate()
For Each r In Me.UsedRange: Record r: Next
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
For Each r In Target: Record r: Next
End Sub
Private Sub Record(r)
r.ID = r.Value & d & Split(r.ID, d)(0)
End Sub
'----------------------------------------------------------------------
'And that's it.
'This solution uses the obscure and almost never used Range.ID
'property, which allows the old values to persist when the workbook
'is saved.
'At any time you can get at the old value of a cell and it will
'indeed be different than a new current value:
With Sheet1
MsgBox .[a1].Value
MsgBox .ValueLast(.[a1])
End With