Search code examples
excelvbainsertoffsetbeforeupdate

Excel VBA - range offset

I'm trying to make a code which after insert/update value in one cell, will go to the next cell like on the picture. Now this code works only one way:

ActiveCell.Offset(1,0).Select ex. from A2 to B2.

What to do to return offset to the cell on the left from B2 to A3 - need loop like on the picture.

Picture

Solution

  • I am not quite sure if I understood what you are looking! I set a range on column A and B until row 10, just for testing.

    This code moves to the next cell on the picture whenever you edit the cell and then you press Enter.

    Put this code on the sheet module

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngColA As Range: Set rngColA = Range("A2:A10")
    Dim rngColB As Range: Set rngColB = Range("B2:B10")
    
    If Not Intersect(Target, rngColA) Is Nothing Then
        Target.Offset(0, 1).Select
        exit sub
    End If

    If Not Intersect(Target, rngColB) Is Nothing Then
        Target.Offset(1, -1).Select
    End If
End Sub