update date when cell is altered

[overbet]

Hi,

I have a question about timestamping. If I change any data in cell H2 can Excel automatically put the date of the change in cell M2 or if I change the data in cell H3265 can Excel input the date I made the change in cell M3265? I think there must be a way to do this. Does anyone know how I can accomplish this? Thank you

Edit: Just realized I have multiple sheets in this workbook. Can this be done to just one particular sheet?

 

[Tom Urtis]

This requires a Worksheet_Change event procedure. Right-click on your worksheet tab, left-click to select View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 8 then exit sub
If target.cells.count > 1 then exit sub
With Target.Offset(0, 5)
.Value = Now
.NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
'or just show the date like you asked for, without the time:
'.NumberFormat = "MM/DD/YYYY"
End With
End Sub

 

[overbet]

Hi Tom, Thank you for the suggestion, but I cant get it to work for me. I pasted the code into a module and altered a cell in column H, but the date didnt change in the corresponding (same row) column M. I have found a couple similar pieces of code searching online, but I cant seem to get any of them to work. Feels like I am missing something. Does it matter if I am pasting the code into a standard module and do I need to reference the sheet name since there are a dozen sheets, but I only want this to work on one sheet? Thanks

 

[Tom Urtis]

I wrote the instructions as clearly as I could, to explain where to paste the code and how to paste it where it should be pasted. Please re-read the second sentence in my first reply. I just tested the code and it works fine.

 

[overbet]

I understand and I followed the instructions. I am not a total noob with excel I just have no VBA writing skills. I do record and piece together parts of recorded macros regularly so I am kind of familiar with the vba editor. I also tried the code in a blank workbook and I couldnt get it to work either. Do you think I could I have a default setting that is preventing it from working for me? I can get help at my office tomorrow if I cant get it tonight. Either way thank you again for your time and efforts. Regards

 

[Tom Urtis]

If you pasted the code into the worksheet module where this activity is taking place, and you are testing it by editing cells one at a time in column H on that worksheet, then the only factor I can think of is that you are using a workbook with the .xlsx extension that would not respond to programming code. Try saving your workbook as macro-enabled (.xlsm extension) with the security setting to at least alert you if programming code is present, or to enable all macros in your workbook's Trust Center options. If you have done all that, and the code is still not doing what you expect, which it does for me here, there is something else going on, but I cannot think what it can be.