Dear experts,

I've a marco in my worksheet which is used to show auto current date and
time when something is filled in column A. The code is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A3:A50")) Is Nothing Then
With Target(1, 4)
.Value = Date
.EntireColumn.AutoFit
End With
With Target(1, 5)
.Value = Time
.EntireColumn.AutoFit
End With
End If
End Sub

However, this time, if I change the target range from column A to C and I
want the auto date and time to be shown in column A & B, I then change the
column index in the code from 4 to -2 for the date and from 5 to -1 for the
time, but it did not work.
Could any experts teach me how to correct the code.

Thanks in advance

Hi

Try
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("C3:C50")) Is Nothing Then
With Target.offset(0, -2)
.Value = Date
.EntireColumn.AutoFit
End With
With Target.offset(0,-1)
.Value = Time
.EntireColumn.AutoFit
End With
End If
End Sub


--
Regards
Roger Govier



"Freshman" wrote in message
...
Dear experts,

I've a marco in my worksheet which is used to show auto current date and
time when something is filled in column A. The code is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A3:A50")) Is Nothing Then
With Target(1, 4)
.Value = Date
.EntireColumn.AutoFit
End With
With Target(1, 5)
.Value = Time
.EntireColumn.AutoFit
End With
End If
End Sub

However, this time, if I change the target range from column A to C and I
want the auto date and time to be shown in column A & B, I then change the
column index in the code from 4 to -2 for the date and from 5 to -1 for
the
time, but it did not work.
Could any experts teach me how to correct the code.

Thanks in advance

Hi Roger,

It works wonderful. Thanks a million. Best regards.

"Roger Govier" wrote:

Hi

Try
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("C3:C50")) Is Nothing Then
With Target.offset(0, -2)
.Value = Date
.EntireColumn.AutoFit
End With
With Target.offset(0,-1)
.Value = Time
.EntireColumn.AutoFit
End With
End If
End Sub


--
Regards
Roger Govier



"Freshman" wrote in message
...
Dear experts,

I've a marco in my worksheet which is used to show auto current date and
time when something is filled in column A. The code is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A3:A50")) Is Nothing Then
With Target(1, 4)
.Value = Date
.EntireColumn.AutoFit
End With
With Target(1, 5)
.Value = Time
.EntireColumn.AutoFit
End With
End If
End Sub

However, this time, if I change the target range from column A to C and I
want the auto date and time to be shown in column A & B, I then change the
column index in the code from 4 to -2 for the date and from 5 to -1 for
the
time, but it did not work.
Could any experts teach me how to correct the code.

Thanks in advance