You get one Worksheet_change event per worksheet.

So you could use something like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'on cell at a time
if target.cells.count 1 then exit sub

if not (intersect(target, me.range("a1:x99")) is nothing then
'do the code for a1:x99
elseif not(intersect(target,me.range("q107:z1233")) is nothing then
'do the code for q107:z1233
end if

End Sub

If your ranges overlap or you want to check a multicelled target, you wouldn't
use this.



Horatio J. Bilge, Jr. wrote:

Would that look this this?
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'a bunch of code here...
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'a bunch of different code here...
End Sub
etc.

"mark" wrote:

Instead of the GoTo, you could call subroutines. It would basically do the
same thing, in the end.... but it makes the code more modular, and therefore
easier to read. If you ever find out you don't need a certain piece, just
get ride of that subroutine, or if you need a new piece, just add a new
subroutine.

"Horatio J. Bilge, Jr." wrote:

I have a Worksheet_Change sub that originally had several levels of If
statements to do different things with different ranges ("If
Application.Intesect(Target,Range("A1:A10")) Is Nothing Then" ...).

It was making my head hurt to keep all of them straight, so I switched to
using GoTo statements, so I could separate the code for different ranges.
Then I read that using multiple GoTo statements is frowned upon as bad
practice, because it makes the code difficult to read.

I wanted to get the opinions of some experience programmers.
Any thoughts?
~ Horatio


--

Dave Peterson