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" <(E-Mail Removed)> skrev i meddelelsen
news:B7E07DAC-C0A3-4CB9-9646-(E-Mail Removed)...
>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
(E-Mail Removed)
"dhstein" <(E-Mail Removed)> wrote in message
news:B7E07DAC-C0A3-4CB9-9646-(E-Mail Removed)...
>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.