Hi - I'm truly sorry if this question has already been answered. I've lost so
much time trying to solve something that seemed so simple, so I'm posting in
the hope that someone can help me. I am trying to make a condition macro so
that when a cell in the B column is not blank, then that day's date will
automatically be in the A cell right next to it. I also need it to remain
that date. Basically, I have a worksheet to track purchases so I need to get
the date to go into the A column as soon as the B column get a title. So for
example:

A B
Today Book Title

Here's the macro that I have so far:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("b:b"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
..Offset(0, 1).ClearContents
Else
With .Offset(.Column, 0, 1)
..NumberFormat = "mm/dd/yyyy"
..Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

I figure it's a simple tweak, but I've been unsuccessful in getting the date
into the A cell. Can anyone help?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Column <> 2 Then exit sub
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -1).ClearContents
Else
With .Offset(0,-1)
.NumberFormat = "mm/dd/yyyy"
.Value = Now
End With
End If
end With
Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy

"RefLib1978" wrote:

> Hi - I'm truly sorry if this question has already been answered. I've lost so
> much time trying to solve something that seemed so simple, so I'm posting in
> the hope that someone can help me. I am trying to make a condition macro so
> that when a cell in the B column is not blank, then that day's date will
> automatically be in the A cell right next to it. I also need it to remain
> that date. Basically, I have a worksheet to track purchases so I need to get
> the date to go into the A column as soon as the B column get a title. So for
> example:
>
> A B
> Today Book Title
>
> Here's the macro that I have so far:
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> With Target
> If .Count > 1 Then Exit Sub
> If Not Intersect(Range("b:b"), .Cells) Is Nothing Then
> Application.EnableEvents = False
> If IsEmpty(.Value) Then
> .Offset(0, 1).ClearContents
> Else
> With .Offset(.Column, 0, 1)
> .NumberFormat = "mm/dd/yyyy"
> .Value = Now
> End With
> End If
> Application.EnableEvents = True
> End If
> End With
> End Sub
>
> I figure it's a simple tweak, but I've been unsuccessful in getting the date
> into the A cell. Can anyone help?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("b:b"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -1).ClearContents
Else

.Offset(0, -1).NumberFormat = "mm/dd/yyyy"
.Offset(0, -1).Value = Now

End If
Application.EnableEvents = True
End If
End With
End Sub


--
Gary''s Student - gsnu200738


"RefLib1978" wrote:

> Hi - I'm truly sorry if this question has already been answered. I've lost so
> much time trying to solve something that seemed so simple, so I'm posting in
> the hope that someone can help me. I am trying to make a condition macro so
> that when a cell in the B column is not blank, then that day's date will
> automatically be in the A cell right next to it. I also need it to remain
> that date. Basically, I have a worksheet to track purchases so I need to get
> the date to go into the A column as soon as the B column get a title. So for
> example:
>
> A B
> Today Book Title
>
> Here's the macro that I have so far:
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> With Target
> If .Count > 1 Then Exit Sub
> If Not Intersect(Range("b:b"), .Cells) Is Nothing Then
> Application.EnableEvents = False
> If IsEmpty(.Value) Then
> .Offset(0, 1).ClearContents
> Else
> With .Offset(.Column, 0, 1)
> .NumberFormat = "mm/dd/yyyy"
> .Value = Now
> End With
> End If
> Application.EnableEvents = True
> End If
> End With
> End Sub
>
> I figure it's a simple tweak, but I've been unsuccessful in getting the date
> into the A cell. Can anyone help?

Thank you so much. It worked! I really appreciate it.

"Gary''s Student" wrote:

> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> With Target
> If .Count > 1 Then Exit Sub
> If Not Intersect(Range("b:b"), .Cells) Is Nothing Then
> Application.EnableEvents = False
> If IsEmpty(.Value) Then
> .Offset(0, -1).ClearContents
> Else
>
> .Offset(0, -1).NumberFormat = "mm/dd/yyyy"
> .Offset(0, -1).Value = Now
>
> End If
> Application.EnableEvents = True
> End If
> End With
> End Sub
>
>
> --
> Gary''s Student - gsnu200738
>
>
> "RefLib1978" wrote:
>
> > Hi - I'm truly sorry if this question has already been answered. I've lost so
> > much time trying to solve something that seemed so simple, so I'm posting in
> > the hope that someone can help me. I am trying to make a condition macro so
> > that when a cell in the B column is not blank, then that day's date will
> > automatically be in the A cell right next to it. I also need it to remain
> > that date. Basically, I have a worksheet to track purchases so I need to get
> > the date to go into the A column as soon as the B column get a title. So for
> > example:
> >
> > A B
> > Today Book Title
> >
> > Here's the macro that I have so far:
> >
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > With Target
> > If .Count > 1 Then Exit Sub
> > If Not Intersect(Range("b:b"), .Cells) Is Nothing Then
> > Application.EnableEvents = False
> > If IsEmpty(.Value) Then
> > .Offset(0, 1).ClearContents
> > Else
> > With .Offset(.Column, 0, 1)
> > .NumberFormat = "mm/dd/yyyy"
> > .Value = Now
> > End With
> > End If
> > Application.EnableEvents = True
> > End If
> > End With
> > End Sub
> >
> > I figure it's a simple tweak, but I've been unsuccessful in getting the date
> > into the A cell. Can anyone help?