Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("K1:K20")) Is Nothing Then
With Target
If .Value = "closed" Then
.Offset(0, 1).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on sheet tab and "View Code"

Copy/paste the code into that sheet module.

Edit range to suit. Alt + q to return to Excel.


Gord Dibben MS Excel MVP


On Wed, 3 Feb 2010 11:56:01 -0800, TechGuy
wrote:

Hi all,

I have a data verification drop down created and I am trying to make the
date poplate in certain cells depending on my drop down choice. The main
thing I have found for this would be the "today()" value. The problem with
this though is that the date changes daily. I need this to be a static value.
Any help would be great. Thanks.

=IF(K8="closed", TODAY())