Sort by selection

overbet

Board Regular
Joined
Jul 9, 2010
Messages
54
Will someone please help me with this? I have been trying for a couple weeks on and off and I keep giving up out of frustration. I am trying to sort by column T, by cells colors for the rows I have selected which changes. I recorded a macro and tried applying solutions from similar questions Ive read while looking for a solution. I know this has been asked many times, but I just cant get it to work for my criteria. This is the recorded macro with the sorting criteria I need followed by the closest solution/explanation I have found.

Code:
Sub Sort_Selection()


    ActiveWorkbook.Worksheets("Live").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Live").Sort.SortFields.Add(Range("T419:T452"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(79, 129 _
        , 189)
    ActiveWorkbook.Worksheets("Live").Sort.SortFields.Add(Range("T419:T452"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(153, _
        255, 204)
    ActiveWorkbook.Worksheets("Live").Sort.SortFields.Add(Range("T419:T452"), _
        xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(153, 0 _
        , 204)
    ActiveWorkbook.Worksheets("Live").Sort.SortFields.Add(Range("T419:T452"), _
        xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(0, 255 _
        , 0)
    With ActiveWorkbook.Worksheets("Live").Sort
        .SetRange Range("A419:AE452")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Code:
Public Sub sSortSelection()


'use the keyword "Selection" for the currently selected range
With ActiveSheet.Sort
    .SortFields.Clear
    'the key you want to use is the column to sort on. I used column 1, which is "A", column "B" is 2, etc
    .SortFields.Add Key:=Selection.Columns(1), Order:=xlDescending
    .SetRange Selection
    .Apply
End With


End Sub
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

overbet

Board Regular
Joined
Jul 9, 2010
Messages
54
Please can anybody help me with this?

Will someone please help me with this? I have been trying for a couple weeks on and off and I keep giving up out of frustration. I am trying to sort by column T, by cells colors for the rows I have selected which changes. I recorded a macro and tried applying solutions from similar questions Ive read while looking for a solution. I know this has been asked many times, but I just cant get it to work for my criteria. This is the recorded macro with the sorting criteria I need followed by the closest solution/explanation I have found.

Code:
Sub Sort_Selection()


    ActiveWorkbook.Worksheets("Live").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Live").Sort.SortFields.Add(Range("T419:T452"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(79, 129 _
        , 189)
    ActiveWorkbook.Worksheets("Live").Sort.SortFields.Add(Range("T419:T452"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(153, _
        255, 204)
    ActiveWorkbook.Worksheets("Live").Sort.SortFields.Add(Range("T419:T452"), _
        xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(153, 0 _
        , 204)
    ActiveWorkbook.Worksheets("Live").Sort.SortFields.Add(Range("T419:T452"), _
        xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(0, 255 _
        , 0)
    With ActiveWorkbook.Worksheets("Live").Sort
        .SetRange Range("A419:AE452")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Code:
Public Sub sSortSelection()


'use the keyword "Selection" for the currently selected range
With ActiveSheet.Sort
    .SortFields.Clear
    'the key you want to use is the column to sort on. I used column 1, which is "A", column "B" is 2, etc
    .SortFields.Add Key:=Selection.Columns(1), Order:=xlDescending
    .SetRange Selection
    .Apply
End With


End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
If I understood correctly, you want to select several rows and sort that selection by the colors you have in column T.
Try the following.


Code:
Sub Sort_Selection_1()
    Dim rng As Range, rngT As Range, ini As Long, fin As Long
    
    Set rng = Selection
    
    ini = rng.Cells(1, 1).Row
    fin = rng.Rows.Count + ini - 1
    Set rngT = Range("T" & ini & ":T" & fin)
    With ActiveWorkbook.Worksheets("Live").Sort
        
        .SortFields.Clear
        .SortFields.Add(rngT, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(79, 129, 189)
        .SortFields.Add(rngT, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(153, 255, 204)
        .SortFields.Add(rngT, xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(153, 0, 204)
        .SortFields.Add(rngT, xlSortOnCellColor, xlDescending, , xlSortNormal).SortOnValue.Color = RGB(0, 255, 0)
        
        .SetRange Range("A" & ini & ":AE" & fin)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 

overbet

Board Regular
Joined
Jul 9, 2010
Messages
54
Wow that is exactly what Ive been trying to get. I cant thank you enough for your help and taking the time. I really have been trying for a few weeks and just couldnt get it. Many thanks
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. I appreciate your kind comments.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,986
Messages
5,526,065
Members
409,685
Latest member
Davetom

This Week's Hot Topics

Top