Combining VBA codes

[RW594]

Hi there!

I'm new to this forum, so please let me know if I'm doing anything wrong!

I'm not versed in the arts of VBA, but have managed to find the codes I need through Google. The codes read:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Or Target.Cells.Count > 1 Then Exit Sub
Target.Offset(0, 1) = Now
End If
End Sub


These codes allow me to automatically insert a time stamp in Column 2, whenever Column 1 is filled or changed. Nice and simple.

I intend to run these codes twice on the same sheet, once for Column 1&2, and once for Column 5&6.

I've tried pasting the codes below themselves, like this:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 5 Or Target.Cells.Count > 1 Then Exit Sub
Target.Offset(0, -1) = Now
End If


If Target.Column <> 5 Or Target.Cells.Count > 1 Then Exit Sub
Target.Offset(0, -1) = Now
End If

End Sub


But I then get a compilation error: end if without block if.

I've googled and read many post about combining VBAs, but still haven't come to grips.

So, many many thanks if you are willing to help!!!

Best,
Ed

 

[Michael M]

Remove both end ifs

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 5 Or Target.Cells.Count > 1 Then Exit Sub
Target.Offset(0, -1) = Now
End If

If Target.Column <> 5 Or Target.Cells.Count > 1 Then Exit Sub
Target.Offset(0, -1) = Now
[color=red]End If[/color]

 

[RW594]

Thank you for the fast response Michael. I've taken out the last End If (below the second part of code), but somehow I'm still getting the compliation error... Am I doing something wrong?

 

[Michael M]

Did you remove BOTH end if's ??

 

[RW594]

Oh, yes, I removed both End Ifs, and I'm no longer getting the compilation error.

However, the second part of codes (column 4&5) doesn't seem to work. The first part functions fine tho...

 

[RW594]

And btw I've noticed an error in the initial codes I'd given you. The combined codes read this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Or Target.Cells.Count > 1 Then Exit Sub
Target.Offset(0, -1) = Now

If Target.Column <> 5 Or Target.Cells.Count > 1 Then Exit Sub
Target.Offset(0, -1) = Now

End Sub

 

[Michael M]

This would be easier

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 And Target.Column <> 5 Or Target.Cells.Count > 1 Then Exit Sub
Target.Offset(0, 1) = Now
End Sub

 

[RW594]

This code is magnificent! Much simpler and way more elegant

Thank you so much Michael, for helping with the problem and for openning my eyes to new possibilities with VBA!!!

Best,
Ed

 

[Michael M]