VBA - amend a macro. Show date and time of cell change/update

[Niki_Ivanov]

Hi,

I want to create a VBA Macro to show me when there is a change in any cell in a certain range.
I found the below and amended to suit my sheet , but it does the trick , but it only works for 1 column not a range of columns .. as you can see the offset is -3 .. and it shows date and time 3 column prior the column in which a cell was amended .. and if i put a range of multiple columns i want it always to show the date and time of change in 1 certain cell rather than -3 columns of the cell amended . I was thinking of creating the same macro for each column and offset -3 , -4 , -5 respectively but can't combine for some reason. ANy help will be appreciated . I hope you explained it right .. thanks !

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("D:D"), Target)
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"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub

 

[tonyyy]

Niki_Ivanov,

It looks as though you always want the date/time stamp to appear in Column A, so you really don't need to use the .Offset method; just set xOffsetColumn to 1 (ie, Column A) and use the Cells(Row,Column) structure rather than Range.Offset...

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.[COLOR=#ff0000]Columns("D:F")[/COLOR], Target) 'Change to match your columns
[COLOR=#ff0000]xOffsetColumn = 1[/COLOR]

If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            [COLOR=#ff0000]Cells(Rng.Row, xOffsetColumn)[/COLOR].Value = Now
            [COLOR=#ff0000]Cells(Rng.Row, xOffsetColumn)[/COLOR].NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Else
            [COLOR=#ff0000]Cells(Rng.Row, xOffsetColumn)[/COLOR].ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

Cheers,

tonyyy

 

[Niki_Ivanov]

Thank you Tonyyy . Works great. Cheers !

 

[tonyyy]

You're very welcome. Glad it worked out...