Highlight active sort header

[muhleebbin]

Is there a way to highlight the cell that is actively sorting a document? Cells B3:H3 have a 'filter' activated but I only use it to sort the respective column as needed. So that it's visually easy to see which one is active, I'd like the header that has an active sort to be highlighted.

Thanks in advance for your assistance!

 

[Jaafar Tribak]

See if this works for you :

1- Add a volatile formula in a remmote cell in the worksheet like =Now() . This will be needed so each time a range field is sorted, the Worksheet Calculate event will automatically highlight the header cell of the respective column(s) with the current sort(s) active.

2- Place this code in the worksheet module:

Option Explicit

Private Sub Worksheet_Calculate()
    Call HighlightSortHeader(Me, vbYellow)
End Sub

Private Sub HighlightSortHeader(ByVal Sh As Worksheet, Optional ByVal Color As Long)

    Dim i As Long
   
    If Not Sh.AutoFilter Is Nothing Then
        With Sh.AutoFilter
            .Range.Resize(1, .Range.Columns.Count).Interior.ColorIndex = 0
            For i = 1 To .Sort.SortFields.Count
                .Sort.SortFields(i).Key(1).Interior.Color = IIf(Color = 0, vbYellow, Color)
            Next i
        End With
    End If

End Sub

 

[muhleebbin]

Thank you so much for this and the explanation of the volatile formula!

Worked perfectly!

 

[muhleebbin]

Hi Jaafar,

Would you mind helping me with a bug I just found? Whenever i'm on another sheet entering data, the volatile formula fires and runs the worksheet calculate and thus the HighlightSortHeader macro. Is there a way to isolate the volatile formula to only calculate when cells B3:H3 are used?

Thanks again for your help!

 

[Jaafar Tribak]

Hi Jaafar,

Would you mind helping me with a bug I just found? Whenever i'm on another sheet entering data, the volatile formula fires and runs the worksheet calculate and thus the HighlightSortHeader macro. Is there a way to isolate the volatile formula to only calculate when cells B3:H3 are used?

Thanks again for your help!

Simply change the Calculate event code as follows :

Private Sub Worksheet_Calculate()
    If ActiveSheet Is Me Then
        Call HighlightSortHeader(Me, vbYellow)
    End If
End Sub

Or maybe if you want a more robust alternative, replace the entire initial code with the following one:

In the Worksheet Module:

Option Explicit

Private Type POINTAPI
    X As Long
    Y As Long
End Type

#If VBA7 Then
    Private Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
#Else
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
#End If

Private Const TARGET_RANGE_ADDRESS = "B3:H3"   '<== change range addrss to suit.

Private Sub Worksheet_Calculate()

    Dim tCurPos As POINTAPI, Obj As Object, oTargetRange As Range
   
    If ActiveSheet Is Me Then
        Call GetCursorPos(tCurPos)
        Set Obj = ActiveWindow.RangeFromPoint(tCurPos.X, tCurPos.Y)
        Set oTargetRange = Range(TARGET_RANGE_ADDRESS)
        If TypeName(Obj) = "Range" Then
            With oTargetRange
            If Union(Obj, oTargetRange).Address = .Address Or _
             Union(Selection, oTargetRange).Address = .Address Then
                Call HighlightSortHeader(Me, vbYellow)
            End If
        End With
        End If
    End If
   
End Sub

Private Sub HighlightSortHeader(ByVal Sh As Worksheet, Optional ByVal Color As Long)

    Dim i As Long

    If Not Sh.AutoFilter Is Nothing Then
        With Sh.AutoFilter
            .Range.Resize(1, .Range.Columns.Count).Interior.ColorIndex = 0
            For i = 1 To .Sort.SortFields.Count
                .Sort.SortFields(i).Key(1).Interior.Color = IIf(Color = 0, vbYellow, Color)
            Next i
        End With
    End If

End Sub

 

[muhleebbin]

Thank you so much for the help here!!

The most annoying part of it all, prior to the fix, was that it would trigger the code even from another workbook. Totally didn't expect it but figure it was because of the volatile function recalculating regardless of workbook.

Thanks again!