MoveAfterReturnDirection

[Rudiji]

Dear all,

I can't find a solution for following problem.
I use Office 2010.
In Excel I have in one sheet 2 ranges (D5:E30 and H5:I30) I can switch between those two by using the spacebar and it jumps to the cell from which it came from previous jump.
The thing I can not find is when I in one of the ranges and I press enter it should jump from the left column to the right one and by the next enter it should jump to the next row of the first column: D5 to E5 to D6 to E6 and so on till I use the spacebar and go to the other range where it should do the same.
I found something here but the problem is that I can't use 2 ranges and I can't use the other cells in the sheet.

Sub SetScroll()
If Selection.Cells.Count = 1 Then
ActiveSheet.ScrollArea = Cells.Address
Application.MoveAfterReturnDirection = xlDown
Else
ActiveSheet.ScrollArea = Selection.Address
Application.MoveAfterReturnDirection = xlToRight
End If
End Sub

Hope someone can help me with this.
Thanks
Rudi

[SamT]

I can switch between those two by using the spacebar and it jumps to the cell from which it came from previous jump.I can switch between those two by using the spacebar and it jumps to the cell from which it came from previous jump.

That must make it very interesting to type two or more words in the same cell.

I use the Tab Key to move to the right and the Enter Key to move down and left. I use the SpaceBar to insert spaces between words.

[Rudiji]

That must make it very interesting to type two or more words in the same cell.

I use the Tab Key to move to the right and the Enter Key to move down and left. I use the SpaceBar to insert spaces between words.

Hi Sam,

Sorry I was maybe not very clear in my explanation but English is not my language.
I don't have to type 2 words in the same cell, I only type a number and move to the next cell.
I'm trying to make a scoreboard for our biljart team and we have 2 tables so the first 2 columns of 25 rows are for the first table and the other 2 columns for the second one.
on both tables could be a match in progress, so if on table 1 a player makes eg. 5 points we type 5 and press enter which moves the active cell to the right when the second player makes eg. 3 points we put 3 and press enter which should bring the active cell 1 down and 1 left so it is ready for player 1 again. When on table 2 a player makes some points I press the spacebar to move to the 2 columns of table 2 and start entering there.
I hope this makes it a little more clear if not I will send you a screenshot.

Regards,
Rudi

[SamT]

Use the Worksheet Change Event sub and a Macro with a Hot Key such as Ctrl+z or Ctrl+b. Don't use a,s,x,c, or v

These two Subs go in the worksheet Code Page. They handle the Enter Key process

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D5:E30", "H5:I30")) Is Nothing Then MoveSelection Target
End Sub

Private Sub MoveSelection(Target As Range)
Select Case Target.Column
Case 4: Target.Offset(, 1).Select
Case 5: Target.Offset(1, -1).Select
Case 8: Target.Offset(, 1).Select
Case 9: Target.Offset(1, -1).Select
End Select
End Sub

Then you must record a Macro, Named "ChangeTeams". Store the Macro in This Workbook. Assign a Keyboard Shortcut. Select a cell before recording. when recording, simply type a letter in the cell and press Enter. This is an easy way to start a sub that has a Keyboard Shortcut.

The Macro recorder will save the Macro code in a standard Module, named "Module1."

Replace the code in the "ChangeTeams" Macro with this

Select Case ActiveCell.Column
Case 4, 5: SelectTeam1
Case 8, 9: SelectTeam2
End Select

Then add these two Subs below the Macro

Private Sub SelectTeam1()
Dim Cel As Range
Set Cel = Cells(Rows.Count, "D").End(xlUp)
If Cel.Offset(, 1) = "" Then
Cel.Offset(, 1).Select
Else
Cel.Offset(1).Select
End If
End Sub

Private Sub SelectTeam2()
Dim Cel As Range
Set Cel = Cells(Rows.Count, "H").End(xlUp)
If Cel.Offset(, 1) = "" Then
Cel.Offset(, 1).Select
Else
Cel.Offset(1).Select
End If
End Sub

This allows the Hotkey to provide the desired SpaceBar process.

[Rudiji]

Dear SamT,

The part for the Enter key works fabulous, thank you very much for that, the "ChangeTeam" however doesn't work but I had this already working in a different way and still in combination with your Enter key code it works fine.
Now I have 1 more request if you allow me, is it possible to make the BG/fill colour of the active cell red but only in the 2 ranges ("D5:E30", "H5:I30"). I tried something but that was a complete failure, it coloured the cell after the enter key was pressed and left it red so every cell with data was coloured red, the idea is that I can see very fast where the active cell is ("D5:E30" or "H5:I30").

Thanks in advance,
Rudi

[SamT]

The Selection Change Event. First uncolor all the cells in the desired region, then color the selected cell

[Rudiji]

It wasn't that simple but could get it done, thanks for everything.

[SamT]

It will be simpler the next time.