Cursor control within event change

[kschmeer]

I have not been able to resolve my problem searching the internet and trying different methods.

My desired action:

On rows 3 – 55, when the cell in column C or D is changed, the next cell when Tab/Enter is pressed is to be in column F of the current row.

My code works if the user presses the Enter key. However if the Tab key is pressed the next active cell is in the row before the current row, column G due to the offset values.

I have tried using activecell, offset, etc. but I have not been able to get this to work correctly for both the Enter and the Tab key.

Any help would be greatly appreciated.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim PaidCells As Range
Dim DepositCells As Range

Set PaidCells = Range("C1:C55")
Set DepositCells = Range("D1:D55")

If ActiveSheet.Name = "Register" Then
'PAID COLUMN: C
If Not Application.Intersect(PaidCells, Range(Target.Address)) Is Nothing Then
ActiveCell.Offset(-1, 3).Select 'Enter key advances a row, offset back that row
'advance 3 columns to column G
End If

'DEPOSIT COLUMN: E
If Not Application.Intersect(DepositCells, Range(Target.Address)) Is Nothing Then
ActiveCell.Offset(-1, 2).Select 'Enter key advances a row, offset back that row
'advance 2 columns to column G
End If
End If

End Sub 'Worksheet_Change

 

[JoeMo]

If you want the active cell to go to column F try changing this:

ActiveCell.Offset(-1, 2).Select

to this:

Cells(Target.Row, "F").Select

 

[kschmeer]

I should have figured I was overthinking it.
Thank you so much. Works great!