I had a similar problem and Tom solved it for me in an earlier thread
with the following....
Hi,
Latest brainteaser,
I have a Excel 2000 pro workbook, in one of the spreadsheets I have
column (E) that uses a dropdown list (Data/Validation/List). I want
the
next column (F) to automatically show the date that the entry to
column
E was last changed or if there has been no change show a generic
start
date....
Is this possible?
--
Alec H
------------------------------------------------------------------------
right click on the sheet tab and select view code. In the resulting
module
paste in code like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
Cells(Target.Row, 6).Value = Now
Cells(Target.Row, 6).NumberFormat = "mm/dd/yyyy hh:mm"
Target.Offset(0, 1).EntireColumn.AutoFit
End If
End Sub
You can run a onetime macro to fill any empty cells
Sub FillWithGeneric()
Set rng = Columns(5).SpecialCells(xlConstants)
For Each cell In rng
With cell.Offset(0, 1)
If IsEmpty(.Value) Then
.Value = DateValue("01/01/2006") + TimeValue("08:00")
.NumberFormat = "mm/dd/yyyy hh:mm"
End If
End With
Next
Columns(5).AutoFit
End Sub
--
Regards,
Tom Ogilvy
Hope this helps.
Alec.
--
Alec H
------------------------------------------------------------------------
Alec H's Profile:
http://www.excelforum.com/member.php...o&userid=31042
View this thread:
http://www.excelforum.com/showthread...hreadid=509999