Jump to specific cell after pressing enter

[MikeWells13]

Hi all,

Is there a way to force Excel to jump to a specific cell after pressing enter?

I have 2 columns of data that I'd like to alterate between quickly, i.e. A2, then D2, then A3, then D3, A4, D4... 

The only workaround I've found is setting up a macro that highlights the cells in that order, but if you click off the screen you have to start again from the beginning (and it isn't set to constantly switch between the rows) so it's not ideal.

Any suggestions?

[#2]

@MikeWells13 

You might do the following:

• Press F5.
• Enter A2:A100,D2:D100 and press Enter or click OK.
• Press Ctrl+1 to activate the Format Cells dialog.
• Activate the Protection tab.
• Clear the Locked check box, then click OK.
• Activate the Review tab of the ribbon.
• Click Protect Sheet, then click OK.
• Tab will now do what you want.
• If you want to edit other cells, click Unprotect Sheet on the Review tab of the ribbon.

[#3]

Thank you.
This works great for Tab. Is there a way to make this do the same when pressing Enter? Still goes down when I do that.
Mike

[#4]

@MikeWells13 

You'd have to do two things:

1) When you protect the sheet, clear the check box "Select locked cells" and leave only "Select unlocked cells" ticked:

2) Select File > Options.

Select Advanced.

Under 'After pressing Enter, move selection', select Right from the Direction dropdown.

Then click OK.

[#5]

So I managed to find a way that sort of worked:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aTabOrd As Variant
Dim i As Long
aTabOrd = Array("W10", "Z10", "W11", "Z11", "W12", "Z12")
For i = LBound(aTabOrd) To UBound(aTabOrd)
If aTabOrd(i) = Target.Address(0, 0) Then
If i = UBound(aTabOrd) Then
Me.Range(aTabOrd(LBound(aTabOrd))).Select
Else
Me.Range(aTabOrd(i + 1)).Select
End If
End If
Next i
End Sub


This works well when entering data, moving to the next cell when I press enter.
However, if I undo because I've made a mistake, the the tab order will skip to the next cell as if the order hadn't stopped. Any suggestions?

[#6]

@MikeWells13 

If you unlock columns A and D and then protect the sheet, allowing only unprotected cells to be selected, then 'tab' will alternate between the two columns.  Whether that creates more problems than it solves is for you to decide.

[#7]

Sorry Hans. For some reason your answers didn't show.