excel - 如何按颜色对表格列进行排序？

Question

当用户更改单元格颜色时，我正在尝试按颜色对表格进行自动排序。

我只想要一栏，所以我把它放在下面Worksheet_Change

Private Sub Worksheet_Change(ByVal Target As Range)

  Set taskPriorityTable = ActiveSheet.ListObjects("TaskPrioritiesTable")
  Set priorityRange = ActiveSheet.ListObjects("TaskPrioritiesTable[PRIORITY]")
  
  If Target.Column = 3 Then
  
    If Not Intersect(Target, priorityRange) Is Nothing Then
        
      Dim ws As Worksheet

      Set ws = ActiveSheet
      
      MsgBox "sorting"

      With taskPriorityTable.Sort 'ws.Sort
        MsgBox "sorting by colors"

        With .SortFields
          .Clear
          .Add(Range("TaskPrioritiesTable[PRIORITY]"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 199, 206)
          .Add(Range("TaskPrioritiesTable[PRIORITY]"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 235, 156)
          .Add(Range("TaskPrioritiesTable[PRIORITY]"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(198, 239, 206)

        End With
        MsgBox "sorting ended"
        .SetRange priorityRange.CurrentRegion
        .Orientation = xlTopToBottom
        .Header = xlYes
        .MatchCase = False
        .SortMethod = xlPinYin
        .Apply

      MsgBox "completed"
      End With

    End If
  MsgBox "text complete"

  End If
End Sub

它正在工作，但它开始崩溃。我将上面代码中的.Add(Rangefrom更改.Add(priorityRange)为 one 。它仍然崩溃或不排序。

我现在确实看到了 msgbox 弹出窗口，但仍然没有排序。RGB 值是正确的。

标签： excelvba

Answer 1

使用 ListObject 对表格进行升序和降序排序

以下代码将从最低值到最高值对表进行排序。

Sub Sort()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range
    
    Set ws = ActiveSheet
    Set tbl = ws.ListObjects("myTable")
    Set rng = Range("myTable[Numbers]")
    
    With tbl.Sort
       .SortFields.Clear
       .SortFields.Add Key:=rng, SortOn:=xlSortOnValues, Order:=xlAscending
       .Header = xlYes
       .Apply
    End With
End Sub

您可以根据需要修改此代码段。