Recording Date/Time from 1 column to another whenever a cell changes

[Olyn01]

Hi everyone!

I found a code on google that automatically generate date and time when a cell changes.
It is working, but I want the date and time to be recorded next to the cell with the old date and time and so on whenever the cell changes.
Can somebody give me a hand on this?

Thanks in advance!


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)


Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer


Set WorkRng = Intersect(Application.ActiveSheet.Range("A:A"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If


End Sub

 

[Olyn01]

@NoSparks thank you! I tried it and it's only working if I just manually put anything on I114. Btw, I am using an Allen Bradley PLC and RSLinx for the software.

 

[NoSparks]

What happens if you add another worksheet to store the value of the A1 cell, I used Sheet2 here,
and use the Worksheet_Calculate event on the A1 sheet ?

Private Sub Worksheet_Calculate()
    Dim nextCol As Long
With Sheets("Sheet1")   'change sheet name as required
    If .Range("A1").Value <> Sheets("Sheet2").Range("A1").Value Then
        nextCol = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
        .Cells(1, nextCol) = Now
        .Cells(1, nextCol).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Sheets("Sheet2").Range("A1").Value = .Range("A1").Value
    End If
End With
End Sub

My brief experience with PLCs was more than 20 years ago... ladder logic on PLC-5 with DOS 6200 software.

 

[Olyn01]

@NoSparks
I added new sheet for this code to work. thank you! I just have this "Runtime error 13 type mismatch" whenever I open the file.

Oh that's too old. hehe