Sort rows by colour

[Apple08]

Hi All

I would like to sort all rows with highlight to the bottom and I have used the macro recorder to create the macro below. However I would like to make this macro generic with unknown number of rows. Please could anyone help?

Private Sub Highlight()


Columns("A:G").Select
    ActiveWorkbook.Worksheets("Change Log").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Change Log").Sort.SortFields.Add2 Key:=Range( _
        "C2:C36"), SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Change Log").Sort.SortFields.Add2 Key:=Range( _
        "D2:D36"), SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Change Log").Sort.SortFields.Add2 Key:=Range( _
        "G2:G36"), SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Change Log").Sort
        .SetRange Range("A1:G36")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[Yongle]

IF you want to format code inside code tags then use <rich/>

    With ActiveWorkbook.Worksheets("Change Log").Sort
        .SetRange Range("A1:G36")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

If you use anything else it gets messed up like this:

    With ActiveWorkbook.Worksheets("Change Log").Sort
        .SetRange Range("A1:G36")
        .Header = xlYes
        .MatchCase = False
[FONT=trebuchet ms]   [COLOR=rgb(184, 49, 47)]     .Orientation = xlTopToBottom[/COLOR][/FONT]
        .SortMethod = xlPinYin
        .Apply
    End With

 

[Apple08]

Thank you for the tips!

Columns("A:G").Select
    ActiveWorkbook.Worksheets("Change Log").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Change Log").Sort.SortFields.Add2 Key:=Range( _
        "C2:C36"), SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Change Log").Sort.SortFields.Add2 Key:=Range( _
        "D2:D36"), SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Change Log").Sort.SortFields.Add2 Key:=Range( _
        "G2:G36"), SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Change Log").Sort
        .SetRange Range("A1:G36")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[Apple08]

I have resolved the issue. Thanks.