VBA sort of excel columns independently by color-CodePudding

Looking for help. I currently have a worksheet with Headers, the columns and the rows continue to increase as data is loaded. I am attempting to use VBA to sort by color. Below is my first pass, but with limited VBA knowledge is is not working. Any assistance would be great.

Sub tgr()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim i As Long

    Set wb = ActiveWorkbook

    For Each ws In wb.Sheets
        Select Case ws.Name
            Case "Sheet1", "Sheet2"
                For i = ws.Columns("A").Column To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                    With ws.Range(ws.Cells(1, i), ws.Cells(ws.Rows.Count, i).End(xlUp))
                        If .Cells.Count > 1 Then
                            .SortFields.Add(rng, xlSortOnCellColor, xlAscending, , xlSortNormal). _
                                SortOnValue.Color = RGB(255, 255, 208)
                            .SetRange rngSort
                            .Header = xlYes
                            .MatchCase = False
                            .Orientation = xlTopToBottom
                            .SortMethod = xlPinYin
                            .Apply
                        End If
                    End With
                Next i
        End Select
    Next ws

End Sub

CodePudding user response：

My read of the Range.Sort is that you aren't going to be able to get where you're trying to go, and Sort object with SortField is what you need with the color sorting.

Here's my slight modification of your code:

Sub tgr()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim i As Long
    Dim rng As Range    'Not absolutely necessary, but helpful to set a range object variable.

    Set wb = ActiveWorkbook

    For Each ws In wb.Sheets
        Select Case ws.Name
            Case "Sheet1", "Sheet2"
                For i = ws.Columns("A").Column To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                    
                    Set rng = ws.Range(ws.Cells(1, i), ws.Cells(ws.Rows.Count, i).End(xlUp))  
' Above, we set the range object variable _
' so we don't have to keep retyping that code.

                    With ws.Sort    'We are applying/creating the Sort object as a property of the worksheet, not the range.
                        If rng.Cells.Count > 1 Then
                            .SortFields.Clear  'Kill any previous sorting criteria and start fresh.
                            .SortFields.Add(Key:=rng, SortOn:=xlSortOnCellColor, _
                                Order:=xlAscending, DataOption:=xlSortNormal).SortOnValue.Color = RGB(255, 255, 208) 
'There's a lot happening in the line above.  We are both adding the new SortField object _
' (as a method of SortFields object -- notice the plural!), and we're setting the SortOnValue property. _
' For clarity, I think a better way to do that is to set another object variable there _
' and break those apart, but my goal is to stay close to your existing code with minimal change.
                            .SetRange rng
                            .Header = xlYes
                            .MatchCase = False
                            .Orientation = xlTopToBottom
                            .SortMethod = xlPinYin
                            .Apply
                        End If
                    End With
                Next i
        End Select
    Next ws

End Sub

This worked for me if the intent is to sort each individual column by itself one at a time by the indicated color floating to the top (is that what you want?).

I find it helps me set more object variables and use them to shorten subsequent code in VBA, hence my "rng" object. Hopefully that gets you where you need to go.