Private Sub Worksheet_Change(ByVal Target As Range)
' Application.ScreenUpdating = False
' Application.EnableEvents = False
Dim cell As Range
For Each cell In Target
If Not Intersect(cell, ListObjects("Tracker_list").ListColumns("ACTION").DataBodyRange) Is Nothing Then
'On Error Resume Next
With Cells(Target.Row, ListObjects("Tracker_list").ListColumns("Date of completion").DataBodyRange.Column)
If IsEmpty(Target) Then
.Value = "ÕÇ"
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Add(Range("Tracker_list[ACTION]"), xlSortOnCellColor, xlAscending, _
, xlSortNormal).SortOnValue.Color = RGB(255, 153, 102)
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Add(Range("Tracker_list[ACTION]"), xlSortOnCellColor, xlAscending, _
, xlSortNormal).SortOnValue.Color = RGB(155, 187, 89)
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Add(Range("Tracker_list[ACTION]"), xlSortOnCellColor, xlAscending, _
, xlSortNormal).SortOnValue.Color = RGB(196, 215, 155)
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Add(Range("Tracker_list[ACTION]"), xlSortOnCellColor, xlAscending, _
, xlSortNormal).SortOnValue.Color = RGB(216, 228, 188)
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Add(Range("Tracker_list[ACTION]"), xlSortOnCellColor, xlAscending, _
, xlSortNormal).SortOnValue.Color = RGB(235, 241, 222)
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Add Key:=Range("Tracker_list[RANK" & Chr(10) & "(list)]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Add(Range("Tracker_list[ACTION]"), xlSortOnCellColor, xlDescending, _
, xlSortNormal).SortOnValue.Color = RGB(166, 166, 166)
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Add Key:=Range("Tracker_list[ACTION]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, CustomOrder:="P,Q,]", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort
.Header = xlYes
.MatchCase = True
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Else
.Value = Now
.EntireColumn.AutoFit
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Add(Range("Tracker_list[ACTION]"), xlSortOnCellColor, xlAscending, _
, xlSortNormal).SortOnValue.Color = RGB(255, 153, 102)
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Add(Range("Tracker_list[ACTION]"), xlSortOnCellColor, xlAscending, _
, xlSortNormal).SortOnValue.Color = RGB(155, 187, 89)
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Add(Range("Tracker_list[ACTION]"), xlSortOnCellColor, xlAscending, _
, xlSortNormal).SortOnValue.Color = RGB(196, 215, 155)
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Add(Range("Tracker_list[ACTION]"), xlSortOnCellColor, xlAscending, _
, xlSortNormal).SortOnValue.Color = RGB(216, 228, 188)
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Add(Range("Tracker_list[ACTION]"), xlSortOnCellColor, xlAscending, _
, xlSortNormal).SortOnValue.Color = RGB(235, 241, 222)
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Add Key:=Range("Tracker_list[RANK" & Chr(10) & "(list)]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Add(Range("Tracker_list[ACTION]"), xlSortOnCellColor, xlDescending, _
, xlSortNormal).SortOnValue.Color = RGB(166, 166, 166)
ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort. _
SortFields.Add Key:=Range("Tracker_list[ACTION]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, CustomOrder:="P,Q,]", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Tracker_list").ListObjects("Tracker_list").Sort
.Header = xlYes
.MatchCase = True
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End With
End If
' Application.ScreenUpdating = True
' Application.EnableEvents = True
Next cell
End Sub
|