编辑录制宏，实现基于字体颜色的表格自定义排序

Got it, let's tweak that recorded macro to sort your table by font color instead of the custom day-of-week list. Here's how you can modify your code step by step:

First, we’ll strip out the custom list logic and replace it with font color-based sorting. Here’s the revised code with explanations:

Updated Macro Code #

Sub SortTableByFontColor()
    ' Clear existing sort rules to avoid conflicts
    ActiveWorkbook.Worksheets("SAP 1").Sort.SortFields.Clear
    
    ' Add font color as the sort key
    ' Replace RGB(255, 0, 0) with the exact font color you want to prioritize
    ActiveWorkbook.Worksheets("SAP 1").Sort.SortFields.Add _
        Key:=Range("A2:A5031"), _
        SortOn:=xlSortOnFontColor, _
        Order:=xlAscending, _
        DataOption:=xlSortNormal, _
        Color:=RGB(255, 0, 0) ' Example: Red font will appear first
    
    ' Configure full sort settings (adjust range to match your table)
    With ActiveWorkbook.Worksheets("SAP 1").Sort
        .SetRange Range("A1:Z5031") ' Update Z to your last table column
        .Header = xlYes ' Keep this if your table has a header row
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Key Changes from Your Original Macro #

• Removed the Application.AddCustomList line since we’re no longer sorting by a predefined value list.
• Changed SortOn:=xlSortOnValues to SortOn:=xlSortOnFontColor to tell Excel to use font color as the sorting criteria.
• Added the Color parameter to specify which font color should come first (replace the RGB value with your target color).
• Updated .SetRange to cover your full table so all columns stay aligned with column A during sorting.

Quick Customization Tips #

• To sort by multiple font colors, add additional SortFields.Add lines for each color, adjusting the Order parameter to set priority.
• To get the exact RGB value of your target font color: select a cell with that color, go to Home > Font > Font Color > More Colors > Custom to copy the RGB values.
• If your table doesn’t have a header row, change .Header = xlYes to .Header = xlNo.

内容的提问来源于stack exchange，提问作者Andreas