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

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
dataViewCodePaste.
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

--
Regards
Roger Govier



"swainstong" wrote in message
...
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