请求协助修改Excel VBA跨工作表排序代码以支持RGB颜色排序

Hey there! Let's tweak your existing VBA code to sort your data by RGB cell background color (we'll use cell fill color as the example—if you need font color instead, I'll note how to switch that later). Here's the revised code, plus breakdowns of the key changes:

Revised Code #

Sub SortAllSheetsByRGBColor()
    Dim WS As Worksheet
    Dim targetRGB As Long
    
    ' Set your target RGB color here (example: yellow = RGB(255,255,0))
    targetRGB = RGB(255, 255, 0)
    
    ' Keep your original copy-paste logic if needed
    ActiveSheet.Range("A1:F1").Copy
    On Error Resume Next
    Application.ScreenUpdating = False
    
    For Each WS In Worksheets
        With WS.Columns("A:F").Sort
            ' Clear existing sort settings to avoid conflicts
            .SortFields.Clear
            ' Add sort rule based on cell background color in column B
            .SortFields.Add _
                Key:=WS.Columns("B"), _
                SortOn:=xlSortOnCellColor, _
                Order:=xlAscending, ' Use xlDescending to put target color at the bottom
                DataOption:=xlSortNormal
            ' Assign your target RGB color to the sort rule
            .SortFields(1).SortOnValue.Color = targetRGB
            ' Assume data has headers in row 1; change to xlNo if no headers
            .Header = xlYes
            ' Apply the sort to the worksheet
            .Apply
        End With
    Next WS
    
    ActiveSheet.Range("G1").PasteSpecial Paste:=xlPasteAll
    Application.ScreenUpdating = True
End Sub

Key Changes Explained #

• Target RGB Color: We added a targetRGB variable where you can define the exact color you want to sort by. Just replace the values in RGB(255,255,0) with your desired red, green, blue values (each between 0-255).
• Sort by Color: Instead of sorting by cell values, we set SortOn:=xlSortOnCellColor to tell Excel to use the cell's background color as the sort key.
  • If you want to sort by font color instead, swap xlSortOnCellColor for xlSortOnFontColor.
• Sort Order: Use xlAscending to place cells with your target color at the top, or xlDescending to push them to the bottom of the range.
• Header Handling: The .Header = xlYes line assumes your data has headers in row 1. If your dataset doesn't have headers, change this to xlNo.
• Cleaner Logic: We used a With block for the Sort object to make the code more readable, and added .SortFields.Clear to avoid conflicts with any pre-existing sort settings.

How to Use #

1. Open your Excel file and press Alt + F11 to launch the VBA Editor.
2. Replace your original SortAllSheets subroutine with this new code.
3. Adjust the targetRGB value to match the RGB color you want to sort by.
4. Run the macro—all worksheets will now sort columns A:F based on the specified RGB color in column B.

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