how to create a cell that appends the current date time when ever the row is edited

fowzan

Board Regular
Joined
Aug 5, 2015
Messages
59
i have an inventory list. the prices keep updating. as of now there is no track to when a row (be it the cost price, sell price, vendor, packing, etc) was edited. i would like to create a new column "Updated On" which stores the date whenever a row is edited.

if a row is updated twice is it possible to store 2 dates like (15-11-15||10-10-14)
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Usually if an action is to be performed automatically when data is changed on a worksheet such as the update you are requesting, it can be done with a Worksheet_Change macro. The only problem is that the data change (the prices in your case) must be done manually. If the price changes are done automatically or due to a formula, then the automatic adding of the date won't work.
 
Upvote 0
Which columns (column letters) contain data that will be manually updated to generate a date?
 
Upvote 0
Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a change or add data in one of your columns B, F or H. Please note that the date will be added to the first unused column in that row. A date will be entered when you change data or add data in one of these columns.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B,F:F,H:H")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim lColumn As Long
    lColumn = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
    Cells(Target.Row, lColumn + 1) = Date
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
this does the job. the only problem is that if value is edited more than once, the date is added to the next cell, and so on.
can it be added to the same cell, without deleting the old value. (latestdate||olddate||oldestdate)
 
Upvote 0
this does the job. the only problem is that if value is edited more than once, the date is added to the next cell, and so on.
can it be added to the same cell, without deleting the old value. (latestdate||olddate||oldestdate)
Give this event code a try...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("B:B,F:F,H:H")) Is Nothing Or Target.Count > 1 Then Exit Sub
  Application.EnableEvents = False
  If Len(Cells(Target.Row, "X").Value) Then
    Cells(Target.Row, "X").Value = Replace(Cells(Target.Row, "X").Value, ")", Format$(Date, "||dd-mm-yy)"))
  Else
    Cells(Target.Row, "X").Value = Format$(Date, "(dd-mm-yy)")
  End If
  Application.EnableEvents = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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