Event Macro help

[Scott]

I would like to record and display the date in a column when a particular
event happens.

For example, I'd like to display the date that each row in Column A displays
"Complete."

If the task in A1 is completed today, I'd like B1 to display "3/22/2010" and
for it to stay that way unless changed or deleted.

Thanks!

 

[Luke M]

'Note that for this to work, column A must be manually changed,
'not a formula creating an output.

Private Sub Worksheet_Change(ByVal Target As Range)
'Is it a cell we care about?
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
'Is it a single cell?
If Target.Count > 1 Then Exit Sub
'Is criteria met?
If UCase(Target.Value) = "COMPLETE" Then
Target.Offset(0, 1).Value = Date
End If
End Sub

 

[Mike H]

Scott,

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
If UCase(Target.Value) = "COMPLETE" Then
Target.Offset(, 1).Value = Date
End If
End If
Application.EnableEvents = True
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Scott]

What happens when the original entry is changed and no longer matches the
"Complete" status? I would want for B1 to clear.