Remove a date in a cell by code

  • Thread starter Thread starter Jock
  • Start date Start date
J

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!
 
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
 
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
Click to expand...

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
 
Back
Top