Filtered column heading row cell color auto

When different column filtered in ms excel , some time face which /which which column filtered. In this time, we try every column tab click and set in this way time waste and brain pressure increases.
If we use vb code easily solve this problem and know which column filtered because a color show in header row cell and identify filtered column.
Vb code is :

Private Sub Worksheet_Calculate()
  Dim af As AutoFilter
  Dim fFilter As Filter
  Dim iFilterCount As Integer
  Dim rHead As Range
  Dim cHead As Range
  Dim lColour As Long
  Dim lColourF As Long
  Dim rFStart As Range
  Application.EnableEvents = False
  
  'You can change these colours and start cell, for your filter settings
  lColour = xlNone  'colour for unfiltered headings
  lColourF = 6 'yellow 'colour for filtered headings
  Set rFStart = ActiveSheet.Range("A1") 'filter range top left cell
  
  Set rHead = rFStart.CurrentRegion.Rows(1).Cells

  If ActiveSheet.AutoFilterMode Then
    Set af = ActiveSheet.AutoFilter

    iFilterCount = 1
    For Each fFilter In af.Filters
      If fFilter.On Then
        With rHead.Cells(1, iFilterCount)
            If .Interior.ColorIndex <> lColourF Then
                .Interior.ColorIndex = lColourF
            End If
        End With
      Else
        With rHead.Cells(1, iFilterCount)
            If .Interior.ColorIndex <> lColour Then
                .Interior.ColorIndex = lColour
            End If
        End With
      End If
      iFilterCount = iFilterCount + 1
    Next fFilter
  Else
    For Each cHead In rHead
        iFilterCount = 1
        With cHead.Cells(1, iFilterCount)
            If .Interior.ColorIndex <> lColour Then
                .Interior.ColorIndex = lColour
            End If
        End With
      iFilterCount = iFilterCount + 1
    Next cHead
  End If
'To use this UDF push Alt+F11 find sheet where you work and paste in the code. Push Alt+Q and save. 
'--------------------Md.Shaiful Islam Talukder--------- 

Download the workbook.

Tweet