More Efficient If Statement

  • Thread starter Thread starter Ken Hudson
  • Start date Start date
K

Ken Hudson

I have the following code as part of a macro. Is there a more efficient (i.e.
less code) way of writing this IF statement?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$G$9" Or Target.Address = "$G$10" Or _
Target.Address = "$G$11" Or Target.Address = "$G$12" Or _
Target.Address = "$G$13" Or Target.Address = "$G$17" Or _
Target.Address = "$G$18" Or Target.Address = "$G$19" Or _
Target.Address = "$G$20" Or Target.Address = "$G$21" Or _
Target.Address = "$G$25" Or Target.Address = "$G$26" Or _
Target.Address = "$G$27" Or Target.Address = "$G$28" Or _
Target.Address = "$G$29" Then
........................

TIA.
 
Try it this way....

If Intersect(Target, Range("G9:G13,G17:G21,G25:G29")) Then

Rick
 
Thanks Rick!
I knew there had to be a better way.
If I could just graduate from "plain vanilla" programming to "chocolate" or
"strawberry", I'd be dangerous.
 
When I tested this code, I received the following error when I entered
anything into a cell in the worksheet that is not part of the IF statement:

Run-time error 91.
Object variable or With block variable not set.

What am I missing?
 
The only thing missing was my full attention... I'm sorry... I was on a
mini-vacation to visit my son this weekend and only popped onto these
newsgroups a couple of times and didn't spend as much time as I should have
in answering the few questions that I did. What I posted was only partially
correct, but I left out the most important part... try it this way

If Not Intersect(Target, Range("G9:G13,G17:G21,G25:G29")) Is Nothing Then

Rick
 
Thanks again.
I figured there was something else missing in the statement and tried:

If Not IsEmpty(Target, Range("G9:G13,G17:G21,G25:G29")) Then

but that didn't do it.
 
Back
Top