Hi
I have Change event macro that sorts a table in G3:O6 when a value in F3:F6
is changed. What I need to happen is that if the range of the entered data is
F13:F18 the table sorted changes to G13:O16. I have been trying to vary the
values in the €˜If Not Intersect(Target, Range("F13:F18")) Then statemnt but
without success. Any help would be appreciated.
Al

As ALWAYS, post your code for comments or

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gramps" wrote in message
...
Hi
I have Change event macro that sorts a table in G3:O6 when a value in
F3:F6
is changed. What I need to happen is that if the range of the entered data
is
F13:F18 the table sorted changes to G13:O16. I have been trying to vary
the
values in the €˜If Not Intersect(Target, Range("F13:F18")) Then statemnt
but
without success. Any help would be appreciated.
Al

post your code
--
HTH...

Jim Thomlinson


"gramps" wrote:

Hi
I have Change event macro that sorts a table in G3:O6 when a value in F3:F6
is changed. What I need to happen is that if the range of the entered data is
F13:F18 the table sorted changes to G13:O16. I have been trying to vary the
values in the €˜If Not Intersect(Target, Range("F13:F18")) Then statemnt but
without success. Any help would be appreciated.
Al

Thanks guys code as below works as far as it goes:-
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 6 Then Exit Sub
gcol = 7: ncol = 14: ocol = 15: lcol = 12
If Not Intersect(Target, Range("F3:F8")) Then
grow = 3: orow = 6
Range(Cells(grow, gcol), Cells(orow, ocol)).Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Cells(grow, ncol),
Cells(orow, ncol)), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Cells(grow, ocol),
Cells(orow, ocol)), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Cells(grow, lcol),
Cells(orow, lcol)), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range(Cells(grow, gcol), Cells(orow, ocol))
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End Sub
This sorts a soccer league table of 4 teams in a round robin event (G3:G6)
1st by points then goal difference then by goals scored, when match results
are entered into E3:F8 (column F is the event trigger). What I want to be
able to do is to extend this to a 2nd group of matches where the results are
entered into F13:F18 and the table is (G13:G18) and depending on the range of
cells the match results are entered the correct table is sorted. I hope
that all makes sense and would thank you in advance for any help you can give.
Al


"Jim Thomlinson" wrote:

post your code
--
HTH...

Jim Thomlinson


"gramps" wrote:

Hi
I have Change event macro that sorts a table in G3:O6 when a value in F3:F6
is changed. What I need to happen is that if the range of the entered data is
F13:F18 the table sorted changes to G13:O16. I have been trying to vary the
values in the €˜If Not Intersect(Target, Range("F13:F18")) Then statemnt but
without success. Any help would be appreciated.
Al

You could use select case to input variables into your sort. I repeat my
offer
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gramps" wrote in message
...
Thanks guys code as below works as far as it goes:-
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 6 Then Exit Sub
gcol = 7: ncol = 14: ocol = 15: lcol = 12
If Not Intersect(Target, Range("F3:F8")) Then
grow = 3: orow = 6
Range(Cells(grow, gcol), Cells(orow, ocol)).Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Cells(grow, ncol),
Cells(orow, ncol)), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Cells(grow, ocol),
Cells(orow, ocol)), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Cells(grow, lcol),
Cells(orow, lcol)), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range(Cells(grow, gcol), Cells(orow, ocol))
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End Sub
This sorts a soccer league table of 4 teams in a round robin event (G3:G6)
1st by points then goal difference then by goals scored, when match
results
are entered into E3:F8 (column F is the event trigger). What I want to be
able to do is to extend this to a 2nd group of matches where the results
are
entered into F13:F18 and the table is (G13:G18) and depending on the range
of
cells the match results are entered the correct table is sorted. I hope
that all makes sense and would thank you in advance for any help you can
give.
Al


"Jim Thomlinson" wrote:

post your code
--
HTH...

Jim Thomlinson


"gramps" wrote:

Hi
I have Change event macro that sorts a table in G3:O6 when a value in
F3:F6
is changed. What I need to happen is that if the range of the entered
data is
F13:F18 the table sorted changes to G13:O16. I have been trying to vary
the
values in the €˜If Not Intersect(Target, Range("F13:F18")) Then
statemnt but
without success. Any help would be appreciated.
Al