Adding the date when another cell is set to Bet.

  • Thread starter Thread starter Steve Cohen
  • Start date Start date
S

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
 
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: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
 
Oops. I got my columns reversed:

Change
If Intersect(Target, Range("D: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)
 
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
 
Hi

On fly! (You have to be sure the right entry on every sheet is chosen! Maybe
using Data.Validation for column F is a wise choice

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

or when something is different on different sheets, then something like
(replace ?'s with right values)

....
If LCase(Target.Value) = "bet" Then
With Target.Offset(0, -2)
.Value = Date
.NumberFormat = "MM/DD/YY"
End With
ElseIf LCase(Target.Value) = "transfer to" Then
With Target.Offset(0, -?)
.Value = ?
.NumberFormat = ?
End With
ElseIf LCase(Target.Value) = "transfer from" Then
With Target.Offset(0, -?)
.Value = ?
.NumberFormat = ?
End With
Else
Target.Offset(0, -2).ClearContents
End If
....


Arvi Laanemets


Steve Cohen said:
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
Dave Peterson said:
Oops. I got my columns reversed:

Change
If Intersect(Target, Range("D: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)
Click to expand...
select
Click to expand...
 
Back
Top