Detecting what value was in a cell prior to a change

Question

Is there a way to detect what a cell use to contain before it was changed. I'm trying to perform actions based on what the previous value was in a cell. I know there's Worksheet_Change but the Target it uses is the new value.

maybe this could help? http://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba — chancea, Mar 03 '15 at 20:39

score 1 · Accepted Answer · answered Mar 03 '15 at 21:36

You can undo the change, populate the undone value to a variable then redo the change like so:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldValue As Variant, NewValue As Variant
NewValue = Target.Value
Application.EnableEvents = False
Application.Undo
OldValue = Target.Value
Target.Value = NewValue
Application.EnableEvents = True
MsgBox OldValue
End Sub

score 0 · Answer 2 · answered Mar 03 '15 at 21:06

We must remember what was in there. Say cell B9 was of interest. In a Standard Module include the single line:

Public OldValue As Variant

and in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub
    v = Target.Value
    MsgBox v & vbCrLf & OldValue
    OldValue = v
End Sub

Detecting what value was in a cell prior to a change

2 Answers2

Linked