Adding the date when another cell is set to Bet.

[Steve Cohen]

I'm trying to figure out a way to get today's date entereind into Cell
D(whatever) when F (whatever) is set to Bet.

I don't want to use the TODAY() funcution because I don't what the date in
that cell to change when the book is closed then opened again.

Thanks

Steve

 

[Dave Peterson]

One way is to use an event macro.

When you change the value in column D to BET, have it put the date in column F.

Rightclick on the worksheet tab that should have this happen and select View
Code.

Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("D")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
If LCase(Target.Value) = "bet" Then
With Target.Offset(0, 2)
.Value = Date
.NumberFormat = "MM/DD/YYYY"
End With
Else
Target.Offset(0, 2).ClearContents
End If

errHandler:
Application.EnableEvents = True

End Sub

 

[Dave Peterson]

Oops. I got my columns reversed:

Change
If Intersect(Target, Range("D")) Is Nothing Then Exit Sub
to
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub

and
Offset(0, 2)
to
Offset(0, -2)

(make sure you get both spots)

 

[Steve Cohen]

Dave;

Thanks that was perfect.
There is just one other thing I forgot.
On 1 worksheet I need it to do the same thing, but when
Cell f is either Transfer to or Transfer from.

I modified what you gave me to work for just one or the other, but I can't
figure out how to do it for both.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
If LCase(Target.Value) = "transfer to" Then
With Target.Offset(0, -2)
.Value = Date
.NumberFormat = "MM/DD/YY"
End With
Else
Target.Offset(0, -2).ClearContents
End If

errHandler:
Application.EnableEvents = True

End Sub


Thanks

Steve