If not Intersect multiple ranges

I have the below code "begining"

Please Login or Register  to view this content.

What I want to do is use multiple ranges like so:

Please Login or Register  to view this content.

However, I want different vba dependant on which of the above triggers.


So.

If Range("A2:A100") triggers,

Do this

If Range("D2:D100") triggers,

Do That



Something like the above.


Or do I run two completely seperate
"Private Sub Worksheet_Change(ByVal Target As Range)" .........

In one sheet?

What happens if the Target Intersects both B and D ? Which takes precedence or would you be looking to both "Do This" and "Do That" ?

Originally Posted by karl1985

how cna it intersect both B & D when the trigger is value change of which you can't change B & D at the same time.

Create a new sheet - highlight B2:D4 - type 1 and press CTRL + ENTER

You might say that's convoluted but equally with same range highlighted you can press the Delete key.

In both cases the Change Event is triggered and in both cases the Target encompasses Columns B & D simultaneously.

If you want the first column to take precedence you can test the column of the resulting Intersect Range

Please Login or Register  to view this content.

Originally Posted by karl1985

in the meatime, i'm thinking

Given the rule of precedence and what little we know regards subsequent actions (DoThis/DoThat) I think a Select Case approach is likely to prove a pretty effective and simple method code wise.

Originally Posted by karl1985

Why is it "case 4"??

I used the Column Number rather than the letter (D being column 4) - generally easier to work with numbers than letters when coding (IMO)

You can add additional Cases... if some Columns are to share the same action you can share the Case,

Please Login or Register  to view this content.

means Cols D & J are to perform the same (subsequent) action

Originally Posted by karl1985

lets hope they don't insert a column then!

If that were the case it follows that given the Intersect references are constant then all approaches are susceptible to error ... ie the fact you're using numbers rather than letter is of no consequence
(you don't have to use the number if you'd prefer not to)

I stand by my point that numbers are "better" but in general terms you would modify the Select Case

Please Login or Register  to view this content.