Macro to move focus after entry in a cell

[Phil H]

Two ranges: D7:G57 and J7:M57



Any time an entry is made in a G cell, focus returns to the next lower D cell. Same for M entries – focus moves to the next lower J cell. For example: When an entry is made in G35, the active cell (focus) changes to D36.

However, when an entry is made in G57 (or M57), the last cell in the range, this rule does not apply – the active cell remains G57 (or M57).

[NoSparks]

Have a look in the attached file. Right click the sheet tab and select view code.

However, when an entry is made in G57 (or M57), the last cell in the range, this rule does not apply – the active cell remains G57 (or M57).

this part will require you to hit enter twice in order to advance the selected cell as per normal.

[Phil H]

NoSparks,

Thanks for your time and the solution to my query. It works perfectly.

Now, users are asking if possible to have focus move across the range. For example: a date is entered in column D, an entry from a dropdown in column E (data validation list), a customer name is typed in column F, and a dollar value entered in column G. Problem is, focus moves down after entries in columns D, E, and F, not across - Excel default. Can you modify the code to move across the row, and leave the move after the G entry as is?

Next, after an entry in G57 (last cell in the first range), have focus move to J7 (first cell in the second range)?

These moves will save users much time by not having to use the mouse to move to the next cell as they post. Guess I should have anticipated this. Mea culpa.

Thanks, Phil

[NoSparks]

Most of what you ask I would never notice, in Excel options I have the direction of move after enter set to Right. This just suits most things I do.
Wish that setting would stick with individual workbooks rather than overall Excel.

Anyhow this should look after what you ask.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("D7:G57,J7:M57")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

'first range
If Target.Column = 4 Then Range("E" & Target.Row).Select
If Target.Column = 5 Then Range("F" & Target.Row).Select
If Target.Column = 6 Then Range("G" & Target.Row).Select
If Target.Column = 7 Then
    If Target.Row = 57 Then
        Range("J7").Select
    Else
        Range("D" & Target.Row + 1).Select
    End If
End If

'second range
If Target.Column = 10 Then Range("K" & Target.Row).Select
If Target.Column = 11 Then Range("L" & Target.Row).Select
If Target.Column = 12 Then Range("M" & Target.Row).Select
If Target.Column = 13 Then
    If Target.Row = 57 Then
        Range("M" & Target.Row).Select
    Else
        Range("J" & Target.Row + 1).Select
    End If
End If
End Sub