Creating a timestamp for changes made across a range of cells in a row

[mariam15]

Hi!

I want to track changes I have made on my worksheet by having a time stamp against each row. I have data from columns C to AX. I would like to have a timestamp in Column AY (only date would also work), if any value changes in any cell between C and AX.

I found a couple of VBA codes which work for changes made in one cell, but I could not tweak them for a range of cells.

I'm a complete novice in writing vba code, so any help would be great!

Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
Are cols C:AX values or do you have any formulae in them?

 

[Tim_Excel_]

Private Sub Worksheet_Change(ByVal Target as Range)

If Intersect(Target, Range("C:AX")) Is Nothing Then Exit Sub

Range("AY1").Value = Now

End Sub

Make sure to place this code in the particular Worksheet module

 

[mariam15]

They mostly have drop-down lists.

 

[mariam15]

Thanks Tim. However, the way I want it to work is, any change between C1 to AX1 should create a timestamp in AY1. While any change in C2 to AX2 should create a time stamp in AY2 and so on for other rows.
With the above code, any change anywhere is creating a timestamp in AY1 only.

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Intersect(Target, Range("C:AX")) Is Nothing Then Exit Sub
   Range("AY" & Target.Row).Value = Now
End Sub

 

[mariam15]

Thanks!! this helps!

 

[Fluff]

You're welcome

 

[0rtli]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Intersect(Target, Range("C:AX")) Is Nothing Then Exit Sub
   Range("AY" & Target.Row).Value = Now
End Sub

Hello Fluff,
It was in 2018 but, can you help with two issues:
1) If the target range is empty target row is also should be empty
2) if copy and paste one row, the timestamp appears, but if I bulk copy&paste more than 1 row the timestamp not works.

 

[Fluff]

As this is a significantly different question, it needs a new thread. Thanks