CODEWITHSUNDEEP
vbaexcel
Mikki85
Mikki85

Reputation: 7

Next cell on Union range VBA

Need a help to find the next cell in the union ranges without a loop.
For now, it's works for me with a loop.

Private Sub Worksheet_Change(ByVal Target As Range)
    Set op = Union(Range("A1", "A2"), Range("A4", "A5"), Range("A9", "A10"))
    If Not Intersect(Target, op) Is Nothing Then
        On Error Resume Next
        For Each cell In op
            If te = 1 Then
                cell.Select
                Exit Sub
            End If
            If cell.Address = Target.Address Then
                te = 1
            End If
        Next cell
        On Error GoTo 0
    End If
End Sub

I didn't find how I get cells in range without "For Each Cell In Range". And didn't find how I know whats a number of selected cell in my specific union range.

What I need to do is to find the next cell within the union that is immediately after the cell whose edit was detected by the Worksheet_Change.

Upvotes: 0

Views: 460

Answers (1)

IronX
IronX

Reputation: 355

Look into Cell Protection. On a Protected Sheet, the User has access to only Unlocked, i.e., Locked = False, cells.

Thus, if an edit is made to an unlocked cell the next unlocked cell will be selected. This requires the following additional settings:

All cells defined by your Union have their Protection set to Locked = False. All non-Union cells have their Protection set to Locked = True (This is a default setting anyway). File -> Options -> Advanced -> checkbox "After pressing Enter, move selection". (This too is a default setting.)

Upvotes: 0

Related Questions