Sort by Value

This procedure sorts data in descending order of TRANS_VALUE in the data shown below.

Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Set tbl = ws.ListObjects("Sales_Table")
Dim sortcolumn As Range
Set sortcolumn = Range("Sales_Table[TRANS_VALUE]")
With tbl.Sort
   .SortFields.Clear
   .SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlDescending
   .Header = xlYes
   .Apply
End With

The code above relates to the data shown below.

Sort by Cell Colour

To sort by cell colour, specify this in the SortOn parameter of the Add method. You then have to specify the colour to sort by using the SortOnValue property of the SortField object (see below).

Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Set tbl = ws.ListObjects("Sales_Table")
Dim sortcolumn As Range
Set sortcolumn = Range("Sales_Table[TRANS_VALUE]")
With tbl.Sort
  .SortFields.Clear
  .SortFields.Add(Key:=sortcolumn, Order:=xlAscending, _
   SortOn:=xlSortOnCellColor).SortOnValue.Color =RGB(255, 255, 0)
  .Header = xlYes
  .Apply
End With

Sort by Icon

To sort by icon, specify this in the SortOn parameterof the Add method. You then have to specify the icon to sort by usingthe Set Icon method of the SortField object.

Dim ws AsWorksheet
Set ws =ActiveSheet
Dim tbl AsListObject
Set tbl =ws.ListObjects("Sales_Table")
Dim sortcolumnAs Range
Set sortcolumn= Range("Sales_Table[TRANS_VALUE]")
With tbl.Sort
   .SortFields.Clear
   .SortFields.Add(Key:=sortcolumn, Order:=xlAscending, _ SortOn:=xlSortOnIcon).SetIcon _ 
    Icon:=ActiveWorkbook.IconSets(xl3TrafficLights1).Item(1)
   .Header =xlYes
   .Apply
End With