VB if cell is in range Excel

Question

I made a VB makro in Excel to execute something if the cell is in a given range but when I execute it it gives me an error and I don't see why.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Boolean
isect = Application.Intersect(Selection, Range("D11:D35"))
If isect Then
    If ActiveCell.Offset(-1, 0) - ActiveCell.Offset(-1, 1) > 2.5 Then
        Range("A1:A1").Value = "ok"
    End If
End If
End Sub

The error is:

Object variable or With block variable not set.

`If Not isect Is Nothing Then` You should also account for the fact that `Target` is not always a single cell: it can be a multiple-cell range... — Tim Williams, Apr 03 '13 at 21:36
You are getting that problem because your code tries to go into a loop because of `Range("A1:A1").Value = "ok"`. I would recommend seeing this http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640 Also Change `Selection` to `Target` EDIT: Also Tim has a valid point that `Target is not always a single cell` So you will have to account for that as well.. — Siddharth Rout, Apr 03 '13 at 21:41
@TimWilliams thanks mate with `If Not isect Is Nothing Then` it works like a charm how can I accept your answer? — pocpoc47, Apr 03 '13 at 21:45
@SiddharthRout you're right excel just crashed.. what else could I use than WorkSheet_Change? — pocpoc47, Apr 03 '13 at 21:56

score 0 · Accepted Answer · answered Apr 03 '13 at 21:42

0

Change the first 3 lines into:

Dim isect As Range
Set isect = Application.Intersect(Selection, Range("D11:D35"))
If Not isect Is Nothing Then

but check also comment from @Siddharth about looping which is very important here.

answered Apr 03 '13 at 21:42

Kazimierz Jawor

18,861
7
35
55

damn you're right what else could I use than WorkSheet_Change? – pocpoc47 Apr 03 '13 at 21:55
just refer to [that idea](http://stackoverflow.com/a/13861640/2143262) by @Siddharth – Kazimierz Jawor Apr 03 '13 at 21:58

score 0 · Answer 2 · edited May 23 '17 at 11:55

Another way without using a Boolean Variable / Selection (Also Incorporating Tim's suggestion as well)...

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False

    If Target.Cells.CountLarge > 1 Then
        MsgBox "More than 1 cell ws changed"
    Else
        If Not Intersect(Target, Range("D11:D35")) Is Nothing Then
            If Target.Offset(-1, 0).Value - Target.Offset(-1, 1).Value > 2.5 Then
                Range("A1").Value = "ok"
            End If
        End If
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Note: Why .CountLarge? See this

VB if cell is in range Excel

2 Answers2