Remove a date in a cell by code
 

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!
--
Traa Dy Liooar

Jock

Remove a date in a cell by code
 

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


"Jock" wrote:

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!
--
Traa Dy Liooar

Jock

Remove a date in a cell by code
 

On Mon, 14 Sep 2009 08:43:02 -0700, Patrick Molloy
wrote:

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

Remove a date in a cell by code
 

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

"Ron Rosenfeld" wrote:

On Mon, 14 Sep 2009 08:43:02 -0700, Patrick Molloy
wrote:

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