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

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" <(E-Mail Removed)> wrote in message
news:53ED7BAD-6F14-4FF9-B38B-(E-Mail Removed)...
> 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

Excellent, thanks John!

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!

Good point.

You might want to change that to:

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

Best regards

John


"JLGWhiz" <(E-Mail Removed)> wrote in message
news:898A1A04-999B-4B63-95C6-(E-Mail Removed)...
> 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!

=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" <(E-Mail Removed)> wrote in message
news:898A1A04-999B-4B63-95C6-(E-Mail Removed)...
> 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!