Automatic Date Aupdate

  • Thread starter Thread starter Kanmi
  • Start date Start date
K

Kanmi

I want to set row above that automatic change date when i change data on each
of the column. For example

Last Update: 6/14/2009 5/19/2009 5/19/2009
(2) 3 4
4 5 6
I want if i change 2 to 100 then 6/14/2009 should change to that
modification day date automatically. If i change it today then 6/14/2009 will
change to 06/23/2009

Meaning this will be modification date. please can anybody help me with
this. Appreciate your time. Thanks.
 
Hi,

You would need to use VBA code for that.

Something on the order of:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A2:D2"))
If Not isect Is Nothing Then
'Your code here
End If
End Sub

1. To add this code to your file, press Alt+F11,
2. In the VBAProject window, top left side, find your sheet name under your
file name and double click it.
3. Paste in or type the code above.
 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = "A2:D2"
Dim cell As Range
On Error GoTo enditall
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Target.Value <> "" Then
With Target.Offset(-1, 0)
.Value = Format(Now, "mm/dd/yyyy hh:mm:ss")
End With
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP
 
Where would insert this code?. Thanks
Gord Dibben said:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = "A2:D2"
Dim cell As Range
On Error GoTo enditall
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Target.Value <> "" Then
With Target.Offset(-1, 0)
.Value = Format(Now, "mm/dd/yyyy hh:mm:ss")
End With
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP
Click to expand...
 
Right-click on the sheet tab and "View Code"

Copy/paste into that module.

Edit the "A2:D2" if necessary.

Alt + q to return to the Excel window.

Make a change to a cell in the range and see the date/time entered in the
cell above.


Gord
 
Back
Top