ExcelBanter - Filter

I don' t believe you can alter the built in functionality of the autofilter.
I don't understand what you mean by display text in the column in another
cell?
if you mean display the filter criteria in another cell, then yes, you could
write a UDF to do that.

Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function

in a cell above the filter (as an example) put in the formula

=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32)

The argument to showfilter designates what column. the Char(subtotal . .
.. ) is used to force the showfilter to be recalculated when you change the
autofilter criteria.

--
Regards,
Tom Ogilvy

--
Regards,
Tom Ogilvy

"Chuck" wrote in message
...
As many in our org can't distinguish which columns are
filtered (is it a blue or black triangle?), is there a
manner in which that can (1) be made more prominent,
maybe red or blinking, or (2) through VBA, display the
text in the column in another cell -- I did try the
Worksheet_SelectionChange(ByVal Target As Range), but
clicking the filter "triangle" doesn't qualify as an
selection change.

Any feedback appreciated.

Thanks