I know very little VBA, so just looking for some guidance please.

I have the below, that works great as is, change a number in 1 column and the column to the right adds the date and time. I would also like to run this code on column 6, tried a lot of things Else, ElseIf, End If etc, with no luck, closest I have managed is the second code, can someone please tell me where I have missed the correct code please.

1. Private Sub Worksheet_Change(ByVal Target As Range)
       If Target.Column <> 3 Then Exit Sub
       With Target.Offset(0, 1)
           .Value = Now
           .NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
       End With
   End Sub
   

2. Private Sub Worksheet_Change(ByVal Target As Range)
       If Target.Column <> 3 Then Exit Sub
       With Target.Offset(0, 1)
           .Value = Now
           .NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
       End With
   
       If Target.Column <> 6 Then Exit Sub
       With Target.Offset(0, 1)
           .Value = Now
           .NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
       End With
   End Sub
   

CodePudding user response：

Your first 'If' prevent that the script continue if the target column is not 3, thus it won't execute when the column is 6.

All you have to do is change your mind, and create conditions for the desired execution.

If Target.Column = 3 Then
    With Target.Offset(0, 1)
    .Value = Now
    .NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
    End With
ElseIf Target.Column = 6 Then
    With Target.Offset(0, 1)
    .Value = Now
    .NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
    End With
End If

Or simplifying:

If Target.Column = 3 Or Target.Column = 6 Then
    With Target.Offset(0, 1)
    .Value = Now
    .NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
    End With
End If