Remove a date in a cell by code

[Jock]

Hi all,
this code will insert the date in column BD when certain criteria is met in
column Y.
Should the cell in Y change from "W" or become blank at a later date, how do
I adapt this code to remove the date that it has put in?

Private Sub Worksheet_Change(ByVal Target As Range)
' Enters date automatically in BD when "Withdraw or Continue" (Y) = "W"
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Y4:Y4000")) Is Nothing Then
With Target
If .Value = "W" Then
.Offset(0, 31).Value = Format(Date, "dd mmm yy")
End If
End With
End If
Application.EnableEvents = True
End Sub

Thanks!

 

[Patrick Molloy]

Private Sub Worksheet_Change(ByVal Target As Range)
' Enters date automatically in BD when "Withdraw or Continue" (Y) = "W"
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Y4:Y4000")) Is Nothing Then
With Target
If .Value = "W" Then
.Offset(0, 31).Value = Format(Date, "dd mmm yy")
ELSE
.Offset(0, 31).Value =""
End If
End With
End If
Application.EnableEvents = True
End Sub

 

[Ron Rosenfeld]

Private Sub Worksheet_Change(ByVal Target As Range)
' Enters date automatically in BD when "Withdraw or Continue" (Y) = "W"
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Y4:Y4000")) Is Nothing Then
With Target
If .Value = "W" Then
.Offset(0, 31).Value = Format(Date, "dd mmm yy")
ELSE
.Offset(0, 31).Value =""
End If
End With
End If
Application.EnableEvents = True
End Sub

Just a thought.

The OP might want to "clear" the target cell rather than set it to ""

e.g:

ELSE
.Offset(0,31).Clear (or ClearContents)
--ron

 

[Patrick Molloy]

then change
..Offset(0, 31).Value =""
to
..Offset(0, 31).ClearContents