Hi,
What I am trying to do is put the System Date in cells in column A whenever
I enter a value in column C. Below is the code I am using

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Target.Offset(0, -2).Value = Date
End Sub

The problem is that when I enter a value in the other cells, it enters the
date in a cell two columns to the left. I know it has to do with the Target
keyword. How do I change that to refer only to cells in column C.
Thank you.
Ayo

Hi Ayo,

Try something like:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Range("C:C")
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(rng, .Cells) Is Nothing Then
If Not IsEmpty(Target) Then
Application.EnableEvents = False
With .Offset(0, -2)
.NumberFormat = "mm/dd/yyyy"
.Value = Date
End With
Application.EnableEvents = True
End If
End If
End With
End Sub
'<<=============


---
Regards,
Norman


"Ayo" <(E-Mail Removed)> wrote in message
news:28BDE66F-E37D-46A4-B8A3-(E-Mail Removed)...
> Hi,
> What I am trying to do is put the System Date in cells in column A
> whenever
> I enter a value in column C. Below is the code I am using
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error Resume Next
> Target.Offset(0, -2).Value = Date
> End Sub
>
> The problem is that when I enter a value in the other cells, it enters the
> date in a cell two columns to the left. I know it has to do with the
> Target
> keyword. How do I change that to refer only to cells in column C.
> Thank you.
> Ayo

Use target.cells() and it can be set to column c (3), but you will need a
loop of sorts to increment the row or function to identify the current row.



"Ayo" wrote:

> Hi,
> What I am trying to do is put the System Date in cells in column A whenever
> I enter a value in column C. Below is the code I am using
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error Resume Next
> Target.Offset(0, -2).Value = Date
> End Sub
>
> The problem is that when I enter a value in the other cells, it enters the
> date in a cell two columns to the left. I know it has to do with the Target
> keyword. How do I change that to refer only to cells in column C.
> Thank you.
> Ayo

Hi Ayoo,

Better would be:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rCell As Range

Set rng = Intersect(Me.Range("C:C"), Target)

If Not rng Is Nothing Then
On Error GoTo XIT
Application.EnableEvents = False
For Each rCell In rng.Cells
With rCell
If Not IsEmpty(Target) Then
With .Offset(0, -2)
.NumberFormat = "mm/ddm/yyyy"
.Value = Date
End With
End If
End With
Next rCell
End If
XIT:
Application.EnableEvents = True
End Sub
'<<=============


---
Regards,
Norman

Hi Ayo,

> If Not IsEmpty(Target) Then


Should read:

If Not IsEmpty(.Value) Then


---
Regards,
Norman

Thanks a bunch Norman. It works great. Thanks.
One other thing, maybe you know what this is.
I am having a problem with formatting a number with a leading zero. I know I
have to use custom format and enter 00000 for the number, i.e., 08667. My
problem is after I have done that and everything looks OK, when I go back to
the cell format dialog window, the Category is changed to Special and the
Locale is changed to Chinese.

"Norman Jones" wrote:

> Hi Ayo,
>
> Try something like:
>
> '=============>>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim rng As Range
>
> Set rng = Range("C:C")
> With Target
> If .Count > 1 Then Exit Sub
> If Not Intersect(rng, .Cells) Is Nothing Then
> If Not IsEmpty(Target) Then
> Application.EnableEvents = False
> With .Offset(0, -2)
> .NumberFormat = "mm/dd/yyyy"
> .Value = Date
> End With
> Application.EnableEvents = True
> End If
> End If
> End With
> End Sub
> '<<=============
>
>
> ---
> Regards,
> Norman
>
>
> "Ayo" <(E-Mail Removed)> wrote in message
> news:28BDE66F-E37D-46A4-B8A3-(E-Mail Removed)...
> > Hi,
> > What I am trying to do is put the System Date in cells in column A
> > whenever
> > I enter a value in column C. Below is the code I am using
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > On Error Resume Next
> > Target.Offset(0, -2).Value = Date
> > End Sub
> >
> > The problem is that when I enter a value in the other cells, it enters the
> > date in a cell two columns to the left. I know it has to do with the
> > Target
> > keyword. How do I change that to refer only to cells in column C.
> > Thank you.
> > Ayo
>
>
>

Hi Ayo,

'---------------
Thanks a bunch Norman. It works great. Thanks.
'---------------

See, however, my last post.

'---------------
One other thing, maybe you know what this is.
I am having a problem with formatting a number with a leading zero. I know I
have to use custom format and enter 00000 for the number, i.e., 08667. My
problem is after I have done that and everything looks OK, when I go back to
the cell format dialog window, the Category is changed to Special and the
Locale is changed to Chinese.
'---------------

This is a new question which has no obvious nexus to your
original post.

For the integrity of the NG, to facilitate subsequent Google
searches and, not least, to maximise your chances of a
useful response, I would suggest that you open a new thread.


---
Regards,
Norman