Option Explicit
Private KeepInMemory As String
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = 'A1' Then
KeepInMemory = Target.Value
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CurrentCell As Range
Set CurrentCell = Me.Range('B' & Me.Range('B500').End(xlUp).Row + 1)
If KeepInMemory <> '' Then
If KeepInMemory <> CurrentCell.Offset(-1, 0) Then CurrentCell = KeepInMemory
End If
End Sub