How to show what autofilter is in effect

[new2access123]

I have been searching for a way to change the color of the autofilter button so it is more visible. I have found that can not be done. But there is a lot of code out there that will is said to change the color of the column head cell when it's filter is in effect. I have down loaded the zip files and opened the sample spread sheets and none have worked.

I am running Office Pro 2003, Excel 2003 (11.5612.5606) Can some one direct me to a sample that will work on my version of Excel so I can copy and past the code to my spread sheet?

I would really appreciate the help

Thanks

 

[HaHoBe]

Hi, new2access123,

to my knowledge by using shapes. But what about using Conditional Fomatting in these cells with an UserDefined Function?

Public Function AF_KRIT(CellRange As Range) As String
'Thomas Ramel

Dim s_Filter        As String

   s_Filter = ""
   On Error GoTo Ende
   With CellRange.Parent.AutoFilter
      With .Filters(CellRange.Column - .Range.Column + 1)
         s_Filter = .Criteria1
         Select Case .Operator
         Case xlAnd
            s_Filter = s_Filter & " AND " & .Criteria2
         Case xlOr
            s_Filter = s_Filter & " OR " & .Criteria2
         End Select
      End With
   End With
Ende:
   AF_KRIT = s_Filter
End Function

Apply FomulaIs and =AF_KRIT(B2)<>"" where B2 is an example for a cell with the header of the Autofilter.

Ciao,
Holger

 

[new2access123]

I am brand new to VBA so i will need a little help getting through this.

* I pressed alt-F11 to open VBA
* In project I select sheet1
* Pasted in the code.
* Save and close VBA

I return to the spread sheet. Column C is a column having 10 rows with numbers 1 - 10 in random order. The first data is in cell C3. The column header is cell C2 in which the autoformat has been set.

* I selected cell C2
* Opened Format, Conditional formatting and set FomulaIs to =AF_KRIT(C2)<>"".
* Clicked the Format button and set a color and pattern
* Clicked OK

When I click the auto filter button snd select a number the column filters as expected and the button triangle turns blue. But the formatting does not appear.

What am I doing wrong?

 

[HaHoBe]

Hi, new2access123,

...
In project I select sheet1
...

If your workbook is called Project you´re fine but the code should not go into the codemodule of a sheet (which is a classmodule of the workbook) but rather in a standard module (which must be inserted and should show a name like Module 1 for the first inserted codemodule).

Ciao,
Holger

 

[new2access123]

That was the problem. I had the code in the wrong place. Your code works great. Thank you very much!