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.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Kanmi" wrote:

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.

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

On Tue, 23 Jun 2009 08:08:01 -0700, Kanmi
wrote:

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.

Where would insert this code?. Thanks
"Gord Dibben" wrote:

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

On Tue, 23 Jun 2009 08:08:01 -0700, Kanmi
wrote:

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.

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

On Tue, 23 Jun 2009 11:35:02 -0700, Kanmi
wrote:


Where would insert this code?. Thanks
"Gord Dibben" wrote:

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

On Tue, 23 Jun 2009 08:08:01 -0700, Kanmi
wrote:

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.