Auto date entry for status change
 

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
------------------------------------------------------------------------
Alec H's Profile: http://www.excelforum.com/member.php...o&userid=31042
View this thread: http://www.excelforum.com/showthread...hreadid=508924

Auto date entry for status change
 

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


"Alec H" wrote in
message ...

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
------------------------------------------------------------------------
Alec H's Profile:
http://www.excelforum.com/member.php...o&userid=31042
View this thread: http://www.excelforum.com/showthread...hreadid=508924

Auto date entry for status change
 

Tom,

Thank you, that "little" piece of code has solved the problem :)

Watch this space for my next bumbling query.......;)


--
Alec H
------------------------------------------------------------------------
Alec H's Profile: http://www.excelforum.com/member.php...o&userid=31042
View this thread: http://www.excelforum.com/showthread...hreadid=508924