VBA Sort Function - Sort by color - No color on top

[nickp512]

Hello!

I've been able to get most of my macro pieced together but the problem that I'm having is getting the actual sort to work. I'm sure it's just something that I'm missing and I'm hoping that you guys can help me out. Here's my code:

Range("A" & ItmLoc & ":" & "P" & ItmLoc).Select
            Range(Selection, Selection.End(xlDown)).Select
                Sheets(Commodity).Sort.SortFields.Add Key:=Selection.Columns(15), SortOn:=xlSortOnCellColor, DataOption:=xlSortNormal
                With ActiveWorkbook.Worksheets(Commodity).Sort
                .Header = xlGuess
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
                End With

So far it selects the range that I want it to, but it won't actually sort by color. I just want cells without color to sort to the top the cells with color are in column "O" or 15.

Thanks!

 

[mole999]

have you recorded it with macro recorder

 

[nickp512]

I had tried it before, and I went back to it again. I found a way to get it to work:

Here's the entire Macro. It's running through a bunch of sheets

Sub SortByColor()
Dim Commodity As String
Dim CommodityLabel As Long
Dim CommodityCount As Long
    Dim PMPur As String
    Dim PMSales As String
    Dim PMPurAcc As String
    Dim PMSalesAcc As String
    Dim PMAdj As String
    Dim CMPur As String
    Dim CMSales As String
    Dim CMPurAcc As String
    Dim CMSalesAcc As String
    Dim CMAdj As String
    Dim OpenPur As String
    Dim OpenSales As String
    Dim OpenPurAcc As String
    Dim OpenSalesAcc As String
    Dim OpenAdj As String
    Dim Itm As Variant
    Dim ItmLoc As Long

    PMPur = "PM OPEN PURCHASES"
    PMSales = "PM OPEN SALES"
    PMPurAcc = "PM PURCHASE ACCRUALS"
    PMSalesAcc = "PM SALES ACCRUALS"
    PMAdj = "PM ADJUSTMENT"
    CMPur = "CM PURCHASES"
    CMSales = "CM SALES"
    CMPurAcc = "CM PURCHASE ACCRUALS"
    CMSalesAcc = "CM SALES ACCRUALS"
    CMAdj = "CM Adjustments"
    OpenPur = "CURRENT OPEN PURCHASES"
    OpenSales = "CURRENT OPEN SALES"
    OpenPurAcc = "OPEN PURCHASE ACCRUALS"
    OpenSalesAcc = "OPEN SALES ACCRUALS"
    OpenAdj = "OPEN ADJUSTMENTS"
    
Application.ScreenUpdating = False
Sheets("Commodity List").Select
CommodityCount = Application.CountA(Range("A:A"))
For CommodityLabel = 2 To CommodityCount
         
        Sheets("Commodity List").Select
        Commodity = Range("A" & CommodityLabel).Select
        Commodity = ActiveCell
        Range("B2").Value = Commodity
        Sheets(Commodity).Select
        Sheets(Commodity).Sort.SortFields.Clear
        
    For Each Itm In Array(PMPur, PMSales, PMPurAcc, PMSalesAcc, PMAdj, CMPur, CMSales, CMPurAcc, CMSalesAcc, CMAdj, OpenPur, OpenSales, OpenPurAcc, OpenSalesAcc, OpenAdj)
    'Itm = PMSales
    
                Sheets(Commodity).Select
                Range("A1").Select
                Cells.Find(What:=Itm, After:=ActiveCell, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False).Activate
        ItmLoc = ActiveCell.Row + 1
        If Range("A" & ItmLoc) = "" Then
            Else
            If Range("A" & ItmLoc + 1) = "" Then
            Else
            Range("A" & ItmLoc & ":" & "P" & ItmLoc).Select
            Range(Selection, Selection.End(xlDown)).Select
               
        Sheets(Commodity).Sort.SortFields.Add Key:=Range("O:O"), _
        SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets(Commodity).Sort
        .SetRange Selection
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        End With

            End If
            End If
        Next Itm
        Next CommodityLabel

End Sub

The biggest problem that I was having was trying to get it to work with the variables that I had. By using the entire column for the range O:O I was able to get around having to define the range for the sort.

It might not be the prettiest code but it works - and I hope that it'll work for others as well.