Microsoft Office Forums - View Single Post - [Solved] Macro to move focus after entry in a cell

NoSparks · 06-18-2015, 01:20 PM

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.

Code:

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