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

"dhstein" skrev i meddelelsen
...
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.

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dhstein" wrote in message
...
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.

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")))


--
Regards,

OssieMac


"dhstein" wrote:

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.

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
--
Cheers,
Ryan


"dhstein" wrote:

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.