highlight filtered cell

[Jagat Pavasia]

I have Filter from A1 to R1,

I want that if i filter any cell (A1 to R1) than highlight it with change the colour .
so that i can easily find that which cell are filtered.

give me developer code etc. please help

 

[Yongle]

Place in SHEET module [right-click sheet tab\ select View Code \ paste VBA into code window \ return to Excel with {ALT}{F11} ]

Sub ColourFilteredHeaders()
    Dim i As Long
    On Error Resume Next
    Range("A1:R1").Interior.ColorIndex = xlNone
    With Me.AutoFilter
        For i = 1 To .Filters.Count
            If .Filters(i).On Then Me.Cells(1, i).Interior.ColorIndex = 50
        Next i
    End With
End Sub

 

[Yongle]

Do you want the macro to run whenever filter is changed?
- here is one way...

Your data finishes in column R
Leave one column empty
Formula goes formula in row 1 in next column (ie T1)

Place this formula in cell T1
=SUBTOTAL(3,A:A)

Paste VBA into SHEET module

Private Sub Worksheet_Calculate()
    Call ColourFilteredHeaders
End Sub

Why it works?
- the subtotal formula is counting FILTERED rows which change when filter changes
- formula is recalculated which triggers Worksheet_Calculate event
- Worksheet_Calculate runs ColourFilteredHeaders

 

[Jagat Pavasia]

did not working, please help

 

[Jagat Pavasia]

error occur with yellow highlight on view code page,

Compile error:
Sub or Function not defined

 

[Jagat Pavasia]

did not working even new excel macro file