Help needed to track user changes on a worksheet in different columns for each user

[Tehflange]

Hi,

Thanks in advance, I'm sure this is doable, I just can't figure it out.

What I am trying to achieve is that on a worksheet, when a user makes a change in column A, column G datestamps the change and column H lists the user.

At the same time, I also want Column B to do the same, but plot the datestamps and username in columns I and J and so on for Columns C, D and E.

I can get the first part to work, with any changes made in A datestamped and username entered in G and F, but then I can't get it to work for B - E.

The code I have cobbled together is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("a7:a200")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
With Application
.ScreenUpdating = False
.EnableEvents = False
With Cells(Target.Row, 7)
.Value = Now
.NumberFormat = "dd/mm/yyyy"
End With
.EnableEvents = True
.ScreenUpdating = True
With Cells(Target.Row, 8)
.Value = Environ("username")
End With
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

How can I get this to then do the same for other 4 columns?

Thanks for any help received,

Dan

[mancubus]

hi Tehflange.
welcome to VBAX.

please use code tags when posting codes. click # button on Quick Reply panel. [ CODE ][ /CODE ] tags will be inserted automatically. paste your code in between these tags.

that said, try below code.



Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("A7:E200")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

With Target
.Offset(, 5 + .Column).Value = Format(Now, "dd/mm/yyyy")
.Offset(, 6 + .Column).Value = Environ("UserName")
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With


End Sub

[Tehflange]

Sorry about the code entering mis-hap, I will learn :)

Thanks for this though, works a treat - I knew it was possible, my brain had just given up!

[mancubus]

you are welcome. one point: If Target = "" Then Exit Sub means if an entry is deleted from A7:E200 its corresponding date and user name values will not bel deleted.