Hi Tommy
You just need to amend Debra's code to take the protection off at the
beginning, and re-instate at the end
Change the password to match what you have used.
Private Sub Worksheet_Calculate()
Dim af As AutoFilter
Dim fFilter As Filter
Dim iFilterCount As Integer
If ActiveSheet.AutoFilterMode Then
ActiveSheet.Unprotect Password:="secret"
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
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFiltering:=True, Password:="secret"
End Sub
--
Regards
Roger Govier
"TommyB" wrote in message
...
Debra...this works great for me as long as my worksheet is unprotected.
When
I protect my worksheet and then attempt to filter a column I get a VB
run-time error 1004, "unable to set the colorindex property of the
Interior
class."
Can you help?
--
TommyB
"Debra Dalgleish" wrote:
The code should be pasted onto the worksheet module for the sheet that
contains the autofilter. Right-click on the sheet tab, choose View Code,
and paste the code where the cursor is flashing.
rontl wrote:
I downloaded the file, and it works great inside the sample. But when
I take
a filtered file i already have, and create a macro module in it, and
paste
your code into it, the coloring only works if I have your workbook
open. It
works very nicely, but it only works when your workbook is open. I
have
AutoFilter turned on, and I inserted "=TODAY()" into a cell to force
recalculation, but still no luck.
Help!
Thanks,
Ron
"Debra Dalgleish" wrote:
There's no way to change the colour of the filter arrows.
If you can use programming, there's a sample file here in which the
heading cell is coloured if a filter is applied to that field:
http://www.contextures.com/excelfiles.html
Under Filters, look for 'Colour Filter Headings'
Or, without programming, you can colour all the headings if any column
in the table has been filtered. For example:
Select all the headings
Choose FormatConditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type:
=COUNTA($A:$A)<SUBTOTAL(3,$A:$A)
Click the Format button
On the Patterns tab, select a colour
Click OK, click OK
sandro wrote:
I use filters in excel extensively, occasionally I do not realise I
have a
filter on a worksheet as the dark blue filter arrows are not
destinctive
enough to the black arrows when the filters are off. How do I change
the
arrows to a brighter colour e.g red
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html