Old cell value

Nice solution to read value in cell AFTER changed.
Needed to have that feature in a tool, I actually did something else, also found here, but I found this solution to be more elegant.

Originally posted in https://stackoverflow.com/a/57225295

CodeFunctionName

What is this?

Public

Tested

Imported

Private Sub Worksheet_Change(ByVal Target As Range)
    Static blnAlreadyBeenHere As Boolean
    'This piece avoid to execute Worksheet_Change again
    If blnAlreadyBeenHere Then
        blnAlreadyBeenHere = False
        Exit Sub
    End If
    'Now, we will store the old and new value
    Dim vOldValue As Variant
    Dim vNewValue As Variant
    'To store new value
    vNewValue = Target.Value
    'Undo to retrieve old value
    'To avoid new Worksheet_Change execution
    blnAlreadyBeenHere = True
    Application.Undo
    'To store old value
    vOldValue = Target.Value
    'To rewrite new value
    'To avoid new Worksheet_Change execution agein
    blnAlreadyBeenHere = True
    Target.Value = vNewValue
    'Done! I've two vaules stored
    Debug.Print vOldValue, vNewValue
End Sub

' Another solution

Public Old_i_Val

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Old_i_Val = Null
    If Target.Cells.Count = 1 Then
        If Target.Column = 11 Then
            Old_i_Val = Target.Value
        End If
    End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim selRow As Long
    Dim selCol As Long
    selRow = Target.Cells(1, 1).Row
    selCol = Target.Cells(1, 1).Column
    If selRow > 3 And selRow < 1004 Then
        If selCol = 11 Then
            If ShBDevin.Cells(selRow, 11).Value2 = "Sold" And Old_i_Val = "Prospect" Then
                ShBDevin.Cells(selRow, 9).Value = Date
            End If
            Old_i_Val = Target.Value ' saving it for next change if any
        End If
    End If
End Sub