Change Value in Cell when date is added

[KateNash]

Hi

How can I change a value in a cell in column A (from whatever it already shows to the word "Completed" when a user enters a closing date in column L?

I can't do it using a simple formula as there are multiple options in column A (e.g. not started, in progress, suspended etc) so I probably need some code which says, IF user enters date in column L then change column A to Completed.

Thanks!

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 12 And IsDate(Target.Value) Then
    Application.EnableEvents = False
    Target.Offset(, -11).Value = "Completed"
    Application.EnableEvents = True
End If
End Sub

 

[KateNash]

Thanks - that works perfectly!!

Cheers

Kate