by Allen Wyatt
(last updated November 14, 2015)
In a large table of data, Ed would like to be able to quickly scan and see if a particular column is being actively filtered. He wonders if there is any way to apply conditional formatting to change the background color of a column when there is a filter in play that is based on that column.
There are a few ways you can approach this task. All of them involve macros, and the purpose of each macro is to determine if a filter is in play for a particular column. One option is to create a function that examines the worksheet for a filter and, if it finds one in place, checking each column in the filtered area to see if there is a filter in play in that column. The following macro does just that.
Sub ColorFilterColumn() Dim flt As Filter Dim iCol As Integer Dim lRow As Long Dim rTemp As Range Dim bFullCol As Boolean ' Set as True if you want entire column shaded bFullCol = False If ActiveSheet.AutoFilterMode Then iCol = ActiveSheet.AutoFilter.Range.Column lRow = ActiveSheet.AutoFilter.Range.Row Application.EnableEvents = False For Each flt In ActiveSheet.AutoFilter.Filters If bFullCol Then Set rTemp = Cells(lRow, iCol).EntireColumn Else Set rTemp = Cells(lRow, iCol) End If If flt.On Then rTemp.Interior.Color = vbYellow Else rTemp.Interior.ColorIndex = xlColorIndexNone End If Set rTemp = Nothing iCol = iCol + 1 Next flt Application.EnableEvents = True End If End Sub
If the macro locates a filter that is at work, it either highlights (in yellow) the first cell in the filtered table or the entire column that has the filter. The determination as to whether a cell or the entire column is highlighted is based on the True/False value assigned to the bFullCol variable.
If you prefer, you could create a function that returns True or False based upon whether a filter is in effect for a particular column. With such a function you could create a conditional formatting rule that formats the column based upon the value returned.
Function bHasFilter(rcell As Range) As Boolean Dim lBaseCol As Long Dim lCol As Long Application.Volatile bHasFilter = False If ActiveSheet.AutoFilterMode Then With ActiveSheet.AutoFilter lBaseCol = .Range.Column lCol = rcell.Column - lBaseCol + 1 If lCol > 0 And lCol <= .Filters.Count Then If .Filters(lCol).On Then bHasFilter = True End If End With End If End Function
To use this function, simply use a formula such as the following in your worksheet or in the conditional formatting rule:
The function first checks to see if there is a filter in effect. If so, then it calculates whether the column of the cell passed to the formula is within the range of filtered columns. (The row referenced in the formula doesn't really matter.) If so, then it checks to see if the filter is turned on for that column.
There are other macro-based approaches that could be used, as well. You can find other resources related to determining what filtering is in effect at the following sites:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13410) applies to Microsoft Excel 2007, 2010, 2013, and 2016.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
If you have a large number of data records, each with an associated date, you might want to filter that data so you see ...Discover More
Filtering can be a powerful way to work with large amounts of data in a worksheet. If you use filtering quite a bit, you ...Discover More
Accessing filtering criteria for use in a formula can be a real need for some worksheet designs. Getting to that ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.