spreadsheet filter alerts

[Guest]

i am using MS Excel 2002 Ver. I have numerous filters set up in a spreadsheet
but wish to be alerted when any one is applied. Is it possible to change the
colour of cells when any one of a number of filters is applied, this would be
in the form of a banner across the top of the spreadsheet to alert me that a
filter has been applied henceinforming me that not all spreadsheet will be
searched for information when requested. Any help appreciated

 

[Roger Govier]

Hi

The following code from Debra Dalgleish will do what you want.

Copy the code below, right click on the sheet tab with your autofilter
data>ViewCode>Paste.
Click back onto youe worksheet
Save the file

Now, when a filter is active, the whole cell containing the autofilter
dropdown will be coloured.

Private Sub Worksheet_Calculate()
'rem Code created by Debra Dalgleish
Dim af As AutoFilter
Dim fFilter As Filter
Dim iFilterCount As Integer

If ActiveSheet.AutoFilterMode Then

Set af = ActiveSheet.AutoFilter
iFilterCount = 1
For Each fFilter In af.Filters
If fFilter.On Then
af.Range.Cells(1, iFilterCount) _
.Interior.ColorIndex = 6
Else
af.Range.Cells(1, iFilterCount) _
.Interior.ColorIndex = xlNone
End If
iFilterCount = iFilterCount + 1
Next fFilter
Else
Rows(1).EntireRow.Interior.ColorIndex = xlNone
End If
End Sub