Event Macro help

  • Thread starter Thread starter Scott
  • Start date Start date
S

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!
 
'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
 
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.
 
What happens when the original entry is changed and no longer matches the
"Complete" status? I would want for B1 to clear.
 
Back
Top