Hi,
I am trying to set up a timestamp at the end of a row of cells that will
enter the current date if any cell in the row is altered or updated. I can
only do it at present with one cell, if I use a range of cells the formula
doesn't work, not sure why. The formula I use is
=IF(R4="","",IF(T4="",NOW(),T4)) If I change the R4 to A4:R4 I get a wrong
#Value.
I'm running Office Pro Excel 2003 edition with SP3.

Thanks for any help.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:R" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

Me.Cells(.Row, "S").Value = Now
Me.Cells(.Row, "S").NumberFormat = "dd mmm yyyy hh:mm:Ss"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Fatwilly" wrote in message
...
Hi,
I am trying to set up a timestamp at the end of a row of cells that will
enter the current date if any cell in the row is altered or updated. I can
only do it at present with one cell, if I use a range of cells the formula
doesn't work, not sure why. The formula I use is
=IF(R4="","",IF(T4="",NOW(),T4)) If I change the R4 to A4:R4 I get a wrong
#Value.
I'm running Office Pro Excel 2003 edition with SP3.

Thanks for any help.

Many thanks Bob it work a treat, great job.
regards
Fatwilly

"Bob Phillips" wrote:


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:R" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

Me.Cells(.Row, "S").Value = Now
Me.Cells(.Row, "S").NumberFormat = "dd mmm yyyy hh:mm:Ss"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Fatwilly" wrote in message
...
Hi,
I am trying to set up a timestamp at the end of a row of cells that will
enter the current date if any cell in the row is altered or updated. I can
only do it at present with one cell, if I use a range of cells the formula
doesn't work, not sure why. The formula I use is
=IF(R4="","",IF(T4="",NOW(),T4)) If I change the R4 to A4:R4 I get a wrong
#Value.
I'm running Office Pro Excel 2003 edition with SP3.

Thanks for any help.