How do I record Date When Cell Changes?-CodePudding

I have below code when the date is changing while any of cells is updated. But the challenge that I have is with the cell where the date is display. I need the data to be updated always only in column E. How to change - xOffsetColumn = 1 to get there?

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), 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

CodePudding user response：

This now works for any intersect range.

I tested the below and it works for me.

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim dateCol%, xOffsetColumn%
Dim Rng As Range, WorkRng As Range
Dim WS As Worksheet

Set WS = ThisWorkbook.Sheets(Target.Parent.Name)

'Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
Set WorkRng = Intersect(Application.ActiveSheet.Range("E:AV"), Target)

'dateCol = 5
dateCol = 4

xOffsetColumn = 3

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"
            WS.Cells(Rng.row, dateCol) = Now()
            WS.Cells(Rng.row, dateCol).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub