Cell update formula
 

Hi

Does anyone know of a formula for Excel 2003 that will stamp the date & time
in a particular cell if anything in that row has changed?

Many thanks

Neil

Cell update formula
 

Hi there,

If you want to do this for a single sheet, add the following to the specific
Sheet object (ie not a separate module):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer
iRow = Target.Row
Cells(iRow, 1).Value = Now()
End Sub


....or, if you want it to effect the whole workbook then add this to the
Workbook object:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim iRow As Integer
iRow = Target.Row
Cells(iRow, 1).Value = Now()
End Sub


Basically, when the change event fires 'Target' becomes a range from which
you can check the row number. You then use that in the cell address (Row x,
Column 1) to place the date and time.

Hope that helps.

Best regards

John

"CDDAH NHS" wrote in message
...
Hi

Does anyone know of a formula for Excel 2003 that will stamp the date &
time
in a particular cell if anything in that row has changed?

Many thanks

Neil

Cell update formula
 

Excellent, thanks John!

Cell update formula
 

Be aware that the Now() will recalculate each time you open the file whether
there is a change or not.

"CDDAH NHS" wrote:

Excellent, thanks John!

Cell update formula
 

Good point.

You might want to change that to:

Cells(iRow, 1).Value = CStr(Now())

Best regards

John


"JLGWhiz" wrote in message
...
Be aware that the Now() will recalculate each time you open the file
whether
there is a change or not.

"CDDAH NHS" wrote:

Excellent, thanks John!

Cell update formula
 

=NOW() will recalculate, but what john is doing dose not put in a formula
it puts in the date and time so it will not recalculate
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"JLGWhiz" wrote in message
...
Be aware that the Now() will recalculate each time you open the file
whether
there is a change or not.

"CDDAH NHS" wrote:

Excellent, thanks John!