Move cursor down and left, if in columns 7

[Tresfjording]

Hi ...
I'm trying to move the cursor one down and one to the left if the cursor i active in column G:G

I input numbers in columns F and G.
On ENTER in column F, I want the cursor to go to the same row in column G
On ENTER in column G, I want the cursor to go one row down, and one cell to the left from active cell.

That is, if i am typing a number in F34, upon ENTER the cursor wil go to G34
And upon ENTER in G34 it will select cell F35.

I have tried to do this in VBA and here is my feeble first try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim colname As Integer
colnmbr = 7

If Columns.Select <> Columns(colnmbr) Then

Application.MoveAfterReturnDirection = xlToRight

Else

ActiveCell.Offset(1, -1).Select
End If

End Sub

The code returns an run-time error 13

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  5/30/2022  8:07:29 PM  EDT

    Select Case Target.Column
        Case 6
            Target.Offset(, 1).Select
        
        Case 7
            Target.Offset(1, -1).Select
    End Select

End Sub

 

[My Aswer Is This]

Or use this they both do the same. Just another way to do it:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  5/30/2022  8:25:29 PM  EDT

    Select Case Target.Column
        Case 6
            Target(, 2).Select
        Case 7
            Target(2, -0).Select
    End Select

End Sub

 

[Tresfjording]

Thank you so much for this soultion!
It worked as a charm.

 

[My Aswer Is This]

Thank you so much for this soultion!
It worked as a charm.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.