Forum Discussion
Automatically adding the date/time to a cell when another cell is updated
Hi Joseph,
This is possible, but you definitely need to a VBA code to do it!
Let's say the five cells you want to target are from cell A1 to E1, so please hover the Mouse over the Worksheet tab, right-click, and select View Code.
Then copy and paste this code into the worksheet code module:
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Or _
Not Intersect(Target, Range("B1")) Is Nothing Or _
Not Intersect(Target, Range("C1")) Is Nothing Or _
Not Intersect(Target, Range("D1")) Is Nothing Or _
Not Intersect(Target, Range("E1")) Is Nothing Then
Target.Offset(1, 0) = Now
End If
End Sub
After that, save the workbook as .xlsm file extension to keep the code saved in it.
If you want to apply it to different cells, simply, you can change the cell ranges in the code and their order in the code does not matter!.
Hope that helps
Haytham Amairah I want to be able to type a date in one cell and have it appear in another cell. It just two cells involved. How can I do this please?
Let's say you want to type a date in A2 on Sheet1 and you want it to appear in D3 on another sheet.
Format D3 the way you want, and enter the following formula in that cell:
=IF(Sheet1!A2="", "", Sheet1!A2)
Haytham - Thank you so much for the quick response on this. This works perfectly and I can see that changing the target offset allows me to change the location of the timestamp.
This is great and exactly what I needed. I really appreciate the help.
Thank you, but I have discovered a simplified version of the code as follows:
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1,B1,C1,D1,E1")) Is Nothing Then
Target.Offset(1, 0) = Now
End If
End SubIt works the same as the previous one, so I recommend you to use it instead.
Regards
Hello Haytham, I know this thread is quite old. And I got this code to work perfectly in my MacBook excel. However, when I increase the Range from ("B2:B10000, through, BK2:BK10000") then for some reason it stops working. Even when reducing it back down to only be 99 lines per column it breaks. Any idea why?
