IF NOT INTERSECT for several non contiguous columns

[PATSYS]

Hi all,

I am composing a worksheet_change code.

How do I express in VBA so that only changes in named ranges:

CWC
PRO
UPL

will trigger the macro?

I used named ranges because the users are inserting columns in the data table.


It is kinda like:

IF NOT INTERSECT(TARGET, RANGE("CWC"))  OR IF NOT INTERSECT(TARGET, RANGE("PRO")) OR IF NOT INTERSECT(TARGET, RANGE("UPL")) THEN

But above is a wrong syntax.

Thanks

 

[VoG]

Possibly

If Not Intersect(Target, Union(Range("CWC"), Range("PRO"), Range("UPL"))) Then

 

[PATSYS]

Possibly

If Not Intersect(Target, Union(Range("CWC"), Range("PRO"), Range("UPL"))) Then

Hi Peter,

Thanks for your reply.

However above code doesn't work. When I enter a value in either of the ranges, it did not fire the code.

It appears that the above expression does not only test intersect of TARGET vs the other ranges (CWC, PRO, UPL) but also the intersection of the ranges (CWC, PRO, UPL) against each other. This is just my opinion.

Can you assist further?

Thanks

 

[PATSYS]

Hi Peter, sorry I missed something in your entire post.

Your code actually works perfectly.

Thank you very much.

 

[Rick Rothstein]

Possibly

If Not Intersect(Target, Union(Range("CWC"), Range("PRO"), Range("UPL"))) Then

First off, it looks like you accidentally omitted the "Is Nothing" part of your statement.

Second, you can omit the Union and form the range directly...

If Not Intersect(Target, Range("CWC,PRO,UPL")) Is Nothing Then

And since the argument to Range is a String, you have the flexibility of forming the range dynamically within your code if need be.