Sort by colour

Hi,

I have the code below that I used Macro Recorder the problem having is sorting the headers as well the data Start at cell "B14" and "B12" and "B13" are headers.

how to modify it that:

1. Sort but not including headers
2. Cells that are blank no colors to be at the bottom. (attaching sample of file)

here is the code:

Sub sort_bycolours()
 ActiveWorkbook.Worksheets("STATEMENT").Sort.SortFields.Clear
 ActiveWorkbook.Worksheets("STATEMENT").Sort.SortFields.Add(Range("B16:B55"), _
 xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
 255, 0)
 ActiveWorkbook.Worksheets("STATEMENT").Sort.SortFields.Add(Range("B16:B55"), _
 xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(146, _
 208, 80)
 ActiveWorkbook.Worksheets("STATEMENT").Sort.SortFields.Add(Range("B16:B55"), _
 xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(112, 48 _
 , 160)
 ActiveWorkbook.Worksheets("STATEMENT").Sort.SortFields.Add(Range("B16:B55"), _
 xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(31, 73 _
 , 125)
 ActiveWorkbook.Worksheets("STATEMENT").Sort.SortFields.Add(Range("B16:B55"), _
 xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(204, _
 153, 0)
 ActiveWorkbook.Worksheets("STATEMENT").Sort.SortFields.Add(Range("B16:B55"), _
 xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(192, 0 _
 , 0)
 ActiveWorkbook.Worksheets("STATEMENT").Sort.SortFields.Add(Range("B16:B55"), _
 xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(148, _
 138, 84)
 ActiveWorkbook.Worksheets("STATEMENT").Sort.SortFields.Add(Range("B16:B55"), _
 xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(218, _
 150, 148)
 ActiveWorkbook.Worksheets("STATEMENT").Sort.SortFields.Add(Range("B16:B55"), _
 xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(141, _
 180, 226)
 ActiveWorkbook.Worksheets("STATEMENT").Sort.SortFields.Add(Range("B16:B55"), _
 xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(38, 38 _
 , 38)
 ActiveWorkbook.Worksheets("STATEMENT").Sort.SortFields.Add(Range("B16:B55"), _
 xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(217, _
 217, 217)
 ActiveWorkbook.Worksheets("STATEMENT").Sort.SortFields.Add(Range("B16:B55"), _
 xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(252, _
 213, 180)
 ActiveWorkbook.Worksheets("STATEMENT").Sort.SortFields.Add(Range("B16:B55"), _
 xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(128, _
 128, 128)
 ActiveWorkbook.Worksheets("STATEMENT").Sort.SortFields.Add2 Key:=Range( _
 "B16:B55"), SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:= _
 xlSortNormal
 With ActiveWorkbook.Worksheets("STATEMENT").Sort
 .SetRange Range("STMTRNG")'("B14").CurrentRegion
 .Header = xlNo
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 End With
End Sub

thank you,

Change this

 .Header = xlNo

to this

 .Header = xlYes

to indicate that your data has headers that not to be sorted.

For no color on the bottom change xlAscending to xlDescending

Building on 6StringJazzer keen observations

Sub sort_bycolours()
    Dim sortRange As Range
    Dim targSheet As Worksheet
    Const sortOrder As Integer = xlDescending
    
    Set targSheet = ActiveWorkbook.Worksheets("STATEMENT")
    Set sortRange = targSheet.Range("B16:B55")
    
    With targSheet.Sort
        .SortFields.Clear
 
        With .SortFields.Add(sortRange, xlSortOnCellColor, sortOrder, , xlSortNormal).SortOnValue
            .Color = RGB(255, 255, 0)
            .Color = RGB(146, 208, 80)
            .Color = RGB(112, 48, 160)
            .Color = RGB(31, 73, 125)
            .Color = RGB(204, 153, 0)
            .Color = RGB(192, 0, 0)
            .Color = RGB(148, 138, 84)
            .Color = RGB(218, 150, 148)
            .Color = RGB(141, 180, 226)
            .Color = RGB(38, 38, 38)
            .Color = RGB(217, 217, 217)
            .Color = RGB(252, 213, 180)
            .Color = RGB(128, 128, 128)
        End With
        
        .SortFields.Add2 key:=sortRange, SortOn:=xlSortOnCellColor, Order:=sortOrder, DataOption:=xlSortNormal
        .SetRange targSheet.Range("B14").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Originally Posted by josros

Microsoft office 365 (office 2016)

Those are two different versions. Are you using both?

Please update your member profile to reflect what version you are actually using.

Hello JosRos:
One reason Error will occur is if the code is attempting to reference a range that it cannot access .
With your code you have it referencing the "ActiveWorkBook".
If you have a second workbook open and its the activeWorkBook when you run the code , then you will get this error. (at least I did when I tried this scenario)
I changed the following line , in this scenario, and the problem went away.

I changed
Set targSheet = ActiveWorkbook.Worksheets("STATEMENT")
to ...
Set targSheet = ThisWorkbook.Worksheets("STATEMENT")

So now you are referencing sheets in workbook that contains the macro vs the activeWorkBook .

NOTE: This is one reason to clean code up. As you notice I only needed to make a single change to code, to test this scenario. In your code you'd have to make updates in over a dozen places,
and missing any single place you'd have only caused more problems. (i.e. the DRY coding principle aka Dont Repeat Yourself)

WARNING:
Once you get this code working, lines you may want to reconsider, if your data is dynamic in size, would be: Set sortRange = targSheet.Range("B16:B55") as well as targSheet.Range("B14").CurrentRegion.
BUT only need to consider if the range of rows and columns is dynamic in size and/or position.

Sub sort_bycolours()
    Dim sortRange As Range
    Dim targSheet As Worksheet
    Const sortOrder As Integer = xlDescending
    
    ' Set targSheet = ActiveWorkbook.Worksheets("STATEMENT") << OLD STATEMENT
    Set targSheet = ThisWorkbook.Worksheets("STATEMENT")
    Set sortRange = targSheet.Range("B16:B55")
    
    With targSheet.Sort
        .SortFields.Clear
 
        With .SortFields.Add(sortRange, xlSortOnCellColor, sortOrder, , xlSortNormal).SortOnValue
            .Color = RGB(255, 255, 0)
            .Color = RGB(146, 208, 80)
            .Color = RGB(112, 48, 160)
            .Color = RGB(31, 73, 125)
            .Color = RGB(204, 153, 0)
            .Color = RGB(192, 0, 0)
            .Color = RGB(148, 138, 84)
            .Color = RGB(218, 150, 148)
            .Color = RGB(141, 180, 226)
            .Color = RGB(38, 38, 38)
            .Color = RGB(217, 217, 217)
            .Color = RGB(252, 213, 180)
            .Color = RGB(128, 128, 128)
        End With
        
        .SortFields.Add2 Key:=sortRange, SortOn:=xlSortOnCellColor, Order:=sortOrder, DataOption:=xlSortNormal
        .SetRange targSheet.Range("B14").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Try this.
Change this

Set sortRange = targSheet.Range("B16:B55")

as

Set sortRange = targSheet.Range("B14")