Automatic Date Aupdate

[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.

 

[Shane Devenshire]

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("A22"))
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.

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = "A22"
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

 

[Kanmi]

Where would insert this code?. Thanks

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = "A22"
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

 

[Gord Dibben]

Right-click on the sheet tab and "View Code"

Copy/paste into that module.

Edit the "A22" 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