Coding the Enter Key-CodePudding

I am new to using excel macro editing. My spread sheet is based on inputting team scores. I have 4 columns. Column A and C are the team #'s and Columns B and D are their scores. So I want to make it so when pressing the enter key in cell B2 instead of going down to B3, it goes two to the right, to D2. and After going to D2, going to B3. I have 4 rows of teams so it continues all the way to D6. I tried using a macro but It was unsuccessful.

That is what I tried but it didn't work. I need some sort of automated way for when pressing enter to select a cell using the Visual Basic Code.

CodePudding user response：

You did not answer my clarification question... If the cell where Enter is pressed has been modified, the code is simple. You already received an answer treating this assumption. Now, if you need the required behavior without modifying the cell, please copy the next event in the active sheet code module (right click on the sheet name and choose 'View Code'):

Option Explicit

Private prevAddress As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim lastRow As Long, rngScore As Range, prCell As Range
    If Target.cells.CountLarge > 1 Then Exit Sub
    
    If prevAddress = "" Then
        If Target.Column = 2 Or Target.Column = 4 Then
            prevAddress = Target.Address: Exit Sub
        End If
    End If
    lastRow = Me.Range("A" & Me.rows.count).End(xlUp).row
    Set rngScore = Union(Me.Range("B2:B" & lastRow   1), Me.Range("D2:D" & lastRow   1))

    If Not Intersect(Target, rngScore) Is Nothing Then
        Application.EnableEvents = False
          If Target.Column = 2 Then
                If Target.Address = "$B$2" And prevAddress <> "D" & lastRow Then   'for CLICKING in B2
                    prevAddress = "$B$2"
                    Application.EnableEvents = True: Exit Sub
                End If
                If Target.Address = "$B$3" And (prevAddress = "$B$2" Or prevAddress = "$D$" & lastRow) Then
                     Target.Offset(-1, 2).Select: prevAddress = Target.Offset(-1, 2).Address
                ElseIf prevAddress = Target.Offset(-1).Address Or _
                    Target.Offset(-2, 2).Address = prevAddress Then
                       Target.Offset(-1, 2).Select: prevAddress = Target.Offset(-1, 2).Address
                Else
                    prevAddress = Target.Address
                End If
          Else 'for column 4:
                If Target.row = lastRow   1 Then 'for the case of the last cell in the accepted range
                    Me.Range("B2").Select
                Else
                    If prevAddress = Target.Offset(-1).Address Then
                        Target.Offset(0, -2).Select: prevAddress = Target.Offset(0, -2).Address
                    Else
                        prevAddress = Target.Address
                    End If
                End If
          End If
        Application.EnableEvents = True
    End If
End Sub

CodePudding user response：

Just double-click the sheet you want to exhibit this behavior under Microsoft Excel Objects in Visual Basic Editor to open the code module that is attached to that workseet:

and add this code. The "option explicit" directive is optional.

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Column
        Case 2: Target.Offset(0, 2).Select
        Case 4: Target.Offset(1, -2).Select
    End Select
End Sub