Naming of column index
 

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

Naming of column index
 

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

Naming of column index
 

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