Moving Selection

  • Thread starter Thread starter Marty Leaf
  • Start date Start date
M

Marty Leaf

I have a worksheet that I need the selection to follow a certain pattern
after Enter is pressed.
Example:
Enter data in A1 press <Enter> selection moves to A2
Enter data in A2 press <Enter> selection moves to A3
Enter data in A3 press <Enter> selection moves to B1
Enter data in B1 press <Enter> selection moves to B3
Enter data in B3 press <Enter> selection moves to C1

We are entering data in a block as follows:
Part #1 Block: D1:K3 (D1, D2, D3, E1, E2, E3, F1, ......, K2, K3)
Part #2 Block: D4:K6
Part #2 Block: D7:K9

Anyway to do this?
Thanks,
Marty
 
Marty,

Copy the event code below, right click on the sheet tab, select "View Code"
and paste the code into the window that appears. This will work fine as long
as you don't have any other event codes. It will move the selection in the
pattern you described. You don't say how far this pattern need to extend
down the worksheet, so you may want to change the check on the row, which is
limited to row 9.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <= 3 Or Target.Column > 11 Then Exit Sub
If Target.Row > 9 Then Exit Sub

If Target.Row Mod 3 = 0 Then
If Target.Column < 11 Then
Target(-1, 2).Select
Else
Target(2, -6).Select
End If
Else
Target(2, 1).Select
End If
End Sub
 
Thank you Bernie,

It works but I was wondering if you could add comments to the lines of code.
I am trying to learn VBA and would like to adjust your code to work with
other spread sheets I have. I understand the 1st two IF statements. I am
having trouble following the other IF statements.

Thanks,
Marty
 
Back
Top