Excel - Lock date in multiple cells

[tungsten222000]

I have an Excel Template .xlt that is distributed and saved by multiple
users as an Excel spreadsheet. I want to have an audit trail visible to
show when entries were made in particular cells. Cells L6, D8, L8 & D10
are to be filled, possibly on different dates, with the relevant users
name. As, and only when, a name is entered into each of those cells I
would like a date (in dd.mm.yyyy format, the format isn't a problem) to
appear in cells P6, H8, P8 & H10 respectively. I want that date to
remain constant even when the .xls is saved, closed and reopened on a
different date.

To explain a little better maybe an example will help:

Name entered in L6 on 29/08/2006 should result in 29.08.2006 appearing
in P6.
Name entered in D8 on 01/09/2006 should result in 01.09.2006 appearing
in H8.
Name entered in L8 on 02/09/2006 should result in 02.09.2006 appearing
in P8.
Name entered in D10 on 03/09/2006 should result in 03/09/2006 appearing
in H10.
If the .xls is opened again on 04/09/2006 the desired result is:

L6 Joe Bloggs P6 29.08.2006
D8 Billy Bloggs H8 01.09.2006
L8 Barry Bloggs P8 02.09.2006
D10 Jack Bloggs H10 03.09.2006

I would appreciate your assistance greatly,
Lindsay

 

[Carim]

Hi Lindsay,

With an event macro stored into the sheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$8" Or Target.Address = "$D$10" _
Or Target.Address = "$L$6" Or Target.Address = "$L$8" Then
Target.Offset(0, 4) = Now
Else
Exit Sub
End If
End Sub


HTH
Cheers
Carim

 

[tungsten222000]

Dear Carim,
That worked really well, thankyou. I had to adjust the offset a little
to cope with some merged cells but after that it worked well.
Thanks for your help.

 

[Carim]

Hi Lindsay,

Glad I could help ...
Cheers
Carim