Automatic Update

dlomas · Apr 29th, 2002, 06:35 AM

One of my co-workers is creating a spreadsheet with a list of values in one column and the date when these values changed in another column. Is there a way to code the date column to automatically change to the current date when the value changes? Any help would very much be appreciated.

Iain Lewis · Apr 29th, 2002, 07:00 AM

Hi,

Put this code in the worksheet module where you need the changes tracked & the date recorded ( let me know if you'd like more explination ). It assumes that you want to check for changes in col A ( 1 ) and then record the date in col B.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If ActiveCell.Column = "1" Then
ActiveCell.Offset(-1, 1).Value = Now()
End If
End Sub

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 29th, 2002, 06:35 AM	#1
dlomas New Member Join Date: Apr 2002 Posts: 22	One of my co-workers is creating a spreadsheet with a list of values in one column and the date when these values changed in another column. Is there a way to code the date column to automatically change to the current date when the value changes? Any help would very much be appreciated.

Apr 29th, 2002, 07:00 AM	#2
Iain Lewis Board Regular Join Date: Apr 2002 Location: Midlands, UK Posts: 217	Hi, Put this code in the worksheet module where you need the changes tracked & the date recorded ( let me know if you'd like more explination ). It assumes that you want to check for changes in col A ( 1 ) and then record the date in col B. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If ActiveCell.Column = "1" Then ActiveCell.Offset(-1, 1).Value = Now() End If End Sub