Compare address to a range

  • Thread starter Thread starter dhstein
  • Start date Start date
D

dhstein

I have this code in an event macro:

If Target.Address = "$BC$3" Or Target.Address = "$BC$4" Or Target.Address =
"$BC$5" Or Target.Address = "$BC$6" Or Target.Address = "$BC$7" Then


Is there a more compact way to write that statement? Thanks for any help on
this.
 
Hi

Look at this:

Set isect = Intersect(Target, Range("BC3:BC7"))
If Not isect Is Nothing Then

or just:

If Not Intersect(Target, Range("BC3:BC7")) Is Nothing Then

Regards,
Per
 
Private Sub Worksheet_Change(ByVal Target As Range)
'does not execute if not in the range
If Intersect(Target, Range("bc3:bc7")) Is Nothing Then Exit Sub
End Sub
'If Target.Address = "$BC$3" Or Target.Address = "$BC$4" Or Target.Address =
'"$BC$5" Or Target.Address = "$BC$6" Or Target.Address = "$BC$7" Then
 
Dim isect As Range

Set isect = Application.Intersect(Target, Range("$BC$3:$BC$7"))

If isect Is Nothing Then
MsgBox "Target NOT in range"
Else
MsgBox "Target IS in range"
End If

You can also test against use multiple ranges. (Note that a space and
underscore at the end of a line is a line break in an otherwise single line
of code.)

Set isect = Application.Intersect _
(Target, Union(Range("$BC$3:$BC$7"), _
Range("$BC$13:$BC$17")))
 
I think this is about as compact as it gets. I hope this helps! If so, let
me know, click "YES" below.

If Not Application.Intersect(Target, Range("BC3:BC7")) Is Nothing Then
' put your code here
End If
 
Back
Top