VBA highlight filtered column (top row only)

[MichaelRSnow]

I have located this code which highlights the whole column and changes all other columns to colour blank, is there a way of adjusting this so it only applies it to the top row (row 1) and not the whole column?

Any help would be much appreciated

[COLOR=blue]Private[/COLOR] [COLOR=blue]Sub[/COLOR] Worksheet_Calculate() 
    ColorAutoFilter 
[COLOR=blue]End Sub[/COLOR] 

[COLOR=blue]Sub[/COLOR] ColorAutoFilter() 
    [COLOR=blue]Dim[/COLOR] FilterNum [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR] 
    [COLOR=blue]With[/COLOR] ActiveSheet 
        [COLOR=blue]If[/COLOR] .AutoFilterMode [COLOR=blue]Then[/COLOR] 
            [COLOR=blue]For[/COLOR] FilterNum = 1 [COLOR=blue]To[/COLOR] .AutoFilter.Filters.Count 
                [COLOR=blue]If[/COLOR] .AutoFilter.Filters(FilterNum).On [COLOR=blue]Then[/COLOR] 
                    .AutoFilter.Range.Columns(FilterNum).Interior.ColorIndex = 6 [COLOR=darkgreen]'yellow[/COLOR]
                [COLOR=blue]Else[/COLOR] 
                    .AutoFilter.Range.Columns(FilterNum).Interior.ColorIndex = xlNone 
                [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR] 
            [COLOR=blue]Next[/COLOR] 
        [COLOR=blue]Else[/COLOR] 
            .Cells.Interior.ColorIndex = xlNone 
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR] 
    [COLOR=blue]End With[/COLOR] 
[COLOR=blue]End Sub[/COLOR]

 

[Andrew Poulsom]

Try eg:

.AutoFilter.Range.Cells(1, FilterNum).Interior.ColorIndex = 6

 

[p45cal]

      If .AutoFilter.Filters(FilterNum).On Then
        .AutoFilter.Range.Columns(FilterNum)[COLOR=#0000cd].Cells(1)[/COLOR].Interior.ColorIndex = 6  'yellow
      Else
        .AutoFilter.Range.Columns(FilterNum)[COLOR=#0000cd].Cells(1)[/COLOR].Interior.ColorIndex = xlNone
      End If

 

[MichaelRSnow]

Thanks Andrew, the result of this is no highlighting occurs what so ever.

 

[MichaelRSnow]

Thanks P45cal, unfortunately same result here, no highlighting occurs following change</SPAN>

 

[Andrew Poulsom]

Thanks Andrew, the result of this is no highlighting occurs what so ever.

This works for me:

Private Sub Worksheet_Calculate()
    ColorAutoFilter
End Sub
Sub ColorAutoFilter()
    Dim FilterNum As Long
    With ActiveSheet
        If .AutoFilterMode Then
            For FilterNum = 1 To .AutoFilter.Filters.Count
                If .AutoFilter.Filters(FilterNum).On Then
                    .AutoFilter.Range.Cells(1, FilterNum).Interior.ColorIndex = 6 'yellow
                Else
                    .AutoFilter.Range.Cells(1, FilterNum).Interior.ColorIndex = xlNone
                End If
            Next
        Else
            .Cells.Interior.ColorIndex = xlNone
        End If
    End With
End Sub

 

[MichaelRSnow]

Sorry both work, just my test file wasn't re-calculating correctly, my filer is on row 3 and this highlights that row not row 1? any ideas how to get this to go to row 1?

 

[Andrew Poulsom]

That would be:

Sub ColorAutoFilter()
    Dim FilterNum As Long
    With ActiveSheet
        If .AutoFilterMode Then
            For FilterNum = 1 To .AutoFilter.Filters.Count
                If .AutoFilter.Filters(FilterNum).On Then
                    .AutoFilter.Range.EntireColumn.Cells(1, FilterNum).Interior.ColorIndex = 6 'yellow
                Else
                    .AutoFilter.Range.EntireColumn.Cells(1, FilterNum).Interior.ColorIndex = xlNone
                End If
            Next
        Else
            .Cells.Interior.ColorIndex = xlNone
        End If
    End With
End Sub

 

[MichaelRSnow]

Perfect Andrew, thank you so much

 

[LindaStL]

This code is working very well for me. Except I have one sheet that has some conditional formats set up and the highlight filtered column codes does not work. Is there a way to get around this aside from deleting my conditional formats? I should also say that if I manually run the macro it works fine but then I have to remember to run the macro each time I filter/unfilter the sheet. That seems to defeat the purpose of the macro.

Thanks for your help.