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

mariam15

New Member
Joined
Jul 24, 2018
Messages
6
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!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi & welcome to MrExcel.
Are cols C:AX values or do you have any formulae in them?
 
Upvote 0
Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
How about
Code:
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
 
Upvote 0
How about
Code:
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.
 
Upvote 0
As this is a significantly different question, it needs a new thread. Thanks
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top