Join multiple VBA code in to single code / run multi cell checks to show last updated date

[Kevw1]

Hi I have the following VBA code against sheet 1 and this is working on a single column D4:D100. But now i need to track the date a change to any cell within

4 different​

columns independently, I can not get this to work. Can anyone help please? I assume I have joined / linked the code incorrectly. Thanks


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D4:D100")) Is Nothing Then Exit Sub
Target.Offset(0, 1) = Now()
Next

If Intersect(Target, Range("G4:G100")) Is Nothing Then Exit Sub
Target.Offset(0, 1) = Now()
Next

If Intersect(Target, Range("J4:J100")) Is Nothing Then Exit Sub
Target.Offset(0, 1) = Now()
Next

If Intersect(Target, Range("N4:N100")) Is Nothing Then Exit Sub
Target.Offset(0, 1) = Now()

End Sub

 

[Osvaldo Palmeiro]

Maybe this:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Range("D4:D100,G4:G100,J4:J100,N4:N100")) Is Nothing Then Exit Sub
 Target.Offset(0, 1) = Now
End Sub

 

[Kevw1]

Hi thanks works great, just wondering if I need it, how would I change the offset value for each column, just thinking if I need to move them all to the right of the sheet to hide from users I could just hide the columns, but would be less likely to be corrupted if I moved to the right of the sheet entirely i guess.
Column D offset by 19,

Column G offset by 20, Column J offset by 21,

Column N offset by 22.
​

​

 

[MickG]

Bit Late !!!!

 

[Osvaldo Palmeiro]

Hi, try this and see if I've understood what you mean.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Range("D4:D100,G4:G100,J4:J100,N4:N100")) Is Nothing Then Exit Sub
 Target.Offset(0, Evaluate("LOOKUP(" & Target.Column & ",{4,7,10,14},{19,20,21,22})")) = Now
End Sub

 

[Kevw1]

Hi that's great all working as expected and i now see how to use the offset better in future thanks