Sorting a dynamic range of data by value of 1 column and color of another column

Question

I am working on a vba to sort a dynamic range of data by cell color of column 2 and value of column 1. The code works okay with sorting just by value, but got a syntax error when I add the code to sort cell color as well. I have searched for a few hours for similar situations over the internet but failed. (perhaps I don't know the correct keywords)

The following is the code that I am working on. Please shed some light on me, thank a bunch!

Dim oneRange As Range
Dim aCell As Range
Dim bCell As Range

Set oneRange = Selection
Set aCell = ActiveCell
Set bCell = ActiveCell.Offset(0, 1)

oneRange.Sort key1:=aCell, Order1:=xlAscending, Header:=xlYes

oneRange.Sort Key2:=bCell, _
   SortOn:=xlSortOnCellColor, _
   Order:=xlAscending, _
    DataOption:=xlSortNormal).SortOnValue.Color = _
    RGB(198, 239, 206)

  End Sub

Answer 1

You are trying to mix Range Sort and WorkSheet Sort.

This line still has the close parenthesis from when you recorded the macro. The macro was using the Worksheet SortFields.Add method.

DataOption:=xlSortNormal).SortOnValue.Color = _

There is no way write an accurate Macro based on your question. Try to avoid using Selection, Activate, ActiveCell, ...etc.

This should get you started:

Sub SortData()
    Dim aColumn As Range, bColumn As Range, Target As Range

    With ActiveWorkbook.Worksheets("Sheet1")
        Set Target = .Range("A1").CurrentRegion
        Set aColumn = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
        Set bColumn = .Range("B2", .Range("B" & .Rows.Count).End(xlUp))

        With .Sort
            .SortFields.Clear
            .SortFields.Add(bColumn, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(198, 239, 206)
            .SortFields.Add Key:=aColumn, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Selection
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub