0

I have two columns,

  1. "Date" (Column H)
  2. "Previous Date in Cell" (Column I)

I would like to use VBA to automatically keep the previous value of "Date" (Column H) and save the value before change in "Previous Date in Cell" (Column I).

Ex. If H2 = 01-06-2018 and the values is changed to 02-06-2018, I2 should than show 01-06-2018 as it is the previous value of the cell.

I would like this code to work for a whole column of cells, not just a single cell. The values in columns H is not manually updated it comes from a formula that looks at a SQL pull out from a third party system. A lot of cells in columns H can therefore be changed simultaneously.

Can you please help me? Thanks in advance.

This is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim newVal As Variant
If Target.Count > 1 Then
    Exit Sub
End If
If Intersect(Range("H:H"), Target) Is Nothing Then
    Exit Sub
End If
Application.EnableEvents = False
Application.ScreenUpdating = False
newVal = Target.Value
Application.Undo
Target.Offset(0, 1).Value = Target.Value
Target.Value = newVal
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

O. Oliver
  • 1
  • 2
  • What have you tried? Can you post your code? – Nathan_Sav Jun 15 '18 at 10:59
  • Use: [Worksheet.Change Event (Excel)](https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2010/ff839775(v=office.14)) – Maciej Los Jun 15 '18 at 11:23
  • Does this answer your question? [How do I get the old value of a changed cell in Excel VBA?](https://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba) – Janine White Feb 08 '22 at 21:41

1 Answers1

0
Private varPrev As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 8 Then
        varPrev = Target.Value
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 8 Then
        Cells(Target.Row, Target.Column + 1).Value = varPrev
    Else
    End If
End Sub
Nathan_Sav
  • 8,466
  • 2
  • 13
  • 20
  • I tried wholsheet_SelectionChange before but it only works when I manually change the value in a cell. I'll post my code here so far here. But again is aslo only working when I manually change the value in the cell. – O. Oliver Jun 17 '18 at 14:58
  • Is it possible some how to always check if the values is changed (Not manually) and the put the old value in column I. Or can the code check for changes when my SQL table is updates? – O. Oliver Jun 17 '18 at 15:23