Need to record a timestamp for every time EVERY cell in a table is updated

[Scruffy Space Gardener]

Hi,

Need some help!

I am running this VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B3:B23")) Is Nothing Then Exit Sub
Range("K" & Target.Row) = Now
End Sub

But!

I also need to run this code for range C3:C23 and D3:D23 and E3:E23 etc. etc.
and place the NOW timestamps in columns L, M, and N and so on and so forth respectively

So basically, I have a table where each cell is updated on a weekly basis and I need a mirrored table to tell me when each of the corresponding cells in the other table was last updated. See attached example tables.

Thanks in advance!

 

[rollis13]

Have a try with these changes. I also added 'Application.EnableEvents" to avoid redundancy (triggering event Worksheet.Change once again when you paste 'Now'):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B3:B23,C3:C23,D3:D23,E3:E23,F3:F23,G3:G23,H3:H23,I3:I23")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Cells(Target.Row, Target.Column).Offset(0, 9) = Now
    Application.EnableEvents = True
End Sub

 

[Scruffy Space Gardener]

Have a try with these changes. I also added 'Application.EnableEvents" to avoid redundancy (triggering event Worksheet.Change once again when you paste 'Now'):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B3:B23,C3:C23,D3:D23,E3:E23,F3:F23,G3:G23,H3:H23,I3:I23")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Cells(Target.Row, Target.Column).Offset(0, 9) = Now
    Application.EnableEvents = True
End Sub

THANK YOU VERY MUCH! Now I can hold people accountable for updating their stuff!

 

[rollis13]

Glad I was able to help .

 

[Scruffy Space Gardener]

So....another follow up on this. The script works nicely when I edit the shared workbook but, I can't seem to get this script to trigger when other users edit the file--which is kind of the whole point.
I have troubleshooted the situation (made sure they are on the desktop version and made sure that all macros are enabled in the trust center). It still wont put a time stamp when they edit... Only when I edit, will it work.

Thoughts?

 

[rollis13]

How do users 'get' to the file ? If it's not via local network then probably that's the problem. In most cases when macros are shared via web these won't work. Only if the file is locally available (downloaded) as you have noticed the macros will be operative.
Sorry, no other thought, it's a very simple macro.