Conditional Formatting Based on Column by Which List Is Sorted

[Zyniker]

I have a worksheet that contains information in 5 Columns (B:F), I would like to conditionally format the header row to highlight the cell by which the data is currently being filtered (e.g., if the data is filtered by the header row of ColumnB, I would like the header cell in ColumnB to be conditionally formatted, let's go with red background just to make this simple).

Further, I would like to create a cell (doesn't really matter where it is on the worksheet) that displays the text in the header cell of the column by which the data in the worksheet is currently being sorted (in other words: if the data is currently being sorted by ColumnB and the header cell in ColumnB says "foo" I want the cell in question to display the text "foo").

Thanks in advance for any help!

 

[jeffreybrown]

Hi Zyniker,

This should get you started. Formula in B1 should be copied across to F1. Place the macro in a standard module. All credit for this macro is credited to Stephen Bullen as noted in the macro itself.

Before Filter

Excel Workbook
	B	C	D	E	F
1
2	Day	Month	Year	Code	Cost
3	Mon	Jan	2007	a	1
4	Tue	Feb	2008	foo	2
5	Wed	Mar	2009	c	3
6	Thu	Apr	2010	d	4
data

After Filter

Excel Workbook
	B	C	D	E	F
1				=foo
2	Day	Month	Year	Code	Cost
4	Tue	Feb	2008	foo	2
data

Function FilterCriteria(Rng As Range) As String
    'By Stephen Bullen
    Dim Filter As String
    Filter = ""
    On Error GoTo Finish
    With Rng.Parent.AutoFilter
        If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
        With .Filters(Rng.Column - .Range.Column + 1)
            If Not .On Then GoTo Finish
            Filter = .Criteria1
            Select Case .Operator
                Case xlAnd
                    Filter = Filter & " AND " & .Criteria2
                Case xlOr
                    Filter = Filter & " OR " & .Criteria2
            End Select
        End With
    End With
    
Finish:
    FilterCriteria = Filter
End Function

 

[Zyniker]

I'll plug that into my worksheet on Monday and see what happens. Thanks.

 

[Lincoln Six Echo]

Wow .... nice trick.
That's very useful when printing several copies of the same sheet using different columns for the sorting!

 

[Zyniker]

It worked perfectly. Thanks.

For future reference, would it be relatively simple to change the code to perform virtually the same function...but altered in order to 'highlight' by what criteria the data is being sorted instead of filtered?