Multiple If Statements or Macro's in Worksheet:View Code
 

Hello,

I am trying to sort two different areas in one worksheet. I am trying to
use the following code but it only works for the first area not the second.

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("h3:n500"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("h3:n500").Sort Key1:=Range("h3")
Application.EnableEvents = True
If Intersect(Range("p3:t500"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("p3:t500").Sort Key1:=Range("p3")
Application.EnableEvents = True

End Sub

Multiple If Statements or Macro's in Worksheet:View Code
 

If the Target doesn't overlap both ranges, your code exits on the first
test.

You'll have to modify it something like this:

If Not Intersect(Range("h3:n500"), Target) Is Nothing Then
... Do the sort on the first range.
End if

If Not Intersect(Range("p3:t500"), Target) Is Nothing Then
... Do the sort on the second range.
End if

If they are mutually exclusive, you could change it to:

If Not Intersect(Range("h3:n500"), Target) Is Nothing Then
... First sort
Elseif Not Intersect(Range("p3:t500"),Target) Is Nothing Then
... Second sort
End if

Scott

Ben Dummar wrote:
Hello,

I am trying to sort two different areas in one worksheet. I am trying to
use the following code but it only works for the first area not the second.

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("h3:n500"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("h3:n500").Sort Key1:=Range("h3")
Application.EnableEvents = True
If Intersect(Range("p3:t500"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("p3:t500").Sort Key1:=Range("p3")
Application.EnableEvents = True

End Sub

Multiple If Statements or Macro's in Worksheet:View Code
 

This may work better...

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("h3:n500"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("h3:n500").Sort Key1:=Range("h3"), Order1:=xlAscending,
Header:=xlGuess, _OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Application.EnableEvents = True
If Intersect(Range("p3:t500"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("p3:t500").Sort Key1:=Range("p3"), Order1:=xlAscending,
Header:=xlGuess, _OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Application.EnableEvents = True
End Sub

Also, if you still want to sort by the second range even if the first range
is missing, you'll want to create a second Sub to perform the second range
sort, as it currently is ending the entire sub once the first set is blank.

"Ben Dummar" wrote:

Hello,

I am trying to sort two different areas in one worksheet. I am trying to
use the following code but it only works for the first area not the second.


Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("h3:n500"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("h3:n500").Sort Key1:=Range("h3")
Application.EnableEvents = True
If Intersect(Range("p3:t500"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("p3:t500").Sort Key1:=Range("p3")
Application.EnableEvents = True

End Sub

Multiple If Statements or Macro's in Worksheet:View Code
 

Sean,

Thanks for the response. I tried the code but it stops the editor.

I think one thing I need to clarify is that I want both list's to operate
independantly of each other. If a name is added to one list it re-sorts that
list but doesn't affect the other list and vice-versa.

Ben

"Sean Timmons" wrote:

This may work better...

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("h3:n500"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("h3:n500").Sort Key1:=Range("h3"), Order1:=xlAscending,
Header:=xlGuess, _OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Application.EnableEvents = True
If Intersect(Range("p3:t500"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("p3:t500").Sort Key1:=Range("p3"), Order1:=xlAscending,
Header:=xlGuess, _OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Application.EnableEvents = True
End Sub

Also, if you still want to sort by the second range even if the first range
is missing, you'll want to create a second Sub to perform the second range
sort, as it currently is ending the entire sub once the first set is blank.

"Ben Dummar" wrote:

Hello,

I am trying to sort two different areas in one worksheet. I am trying to
use the following code but it only works for the first area not the second.


Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("h3:n500"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("h3:n500").Sort Key1:=Range("h3")
Application.EnableEvents = True
If Intersect(Range("p3:t500"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("p3:t500").Sort Key1:=Range("p3")
Application.EnableEvents = True

End Sub

Multiple If Statements or Macro's in Worksheet:View Code
 

Then put each block in an:
If Not Intersect(...) Is Nothing Then
...
End if

Scott

Ben Dummar wrote:
Sean,

Thanks for the response. I tried the code but it stops the editor.

I think one thing I need to clarify is that I want both list's to operate
independantly of each other. If a name is added to one list it re-sorts that
list but doesn't affect the other list and vice-versa.

Ben

"Sean Timmons" wrote:

This may work better...

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("h3:n500"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("h3:n500").Sort Key1:=Range("h3"), Order1:=xlAscending,
Header:=xlGuess, _OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Application.EnableEvents = True
If Intersect(Range("p3:t500"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("p3:t500").Sort Key1:=Range("p3"), Order1:=xlAscending,
Header:=xlGuess, _OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Application.EnableEvents = True
End Sub

Also, if you still want to sort by the second range even if the first range
is missing, you'll want to create a second Sub to perform the second range
sort, as it currently is ending the entire sub once the first set is blank.

"Ben Dummar" wrote:

Hello,

I am trying to sort two different areas in one worksheet. I am trying to
use the following code but it only works for the first area not the second.


Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("h3:n500"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("h3:n500").Sort Key1:=Range("h3")
Application.EnableEvents = True
If Intersect(Range("p3:t500"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("p3:t500").Sort Key1:=Range("p3")
Application.EnableEvents = True

End Sub