Gantt Charts In Excel. Color AutoFilter Criteria Fields

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download

3 GREAT NEW & WELL PRICED PRODUCTS

1) Micro-Charts : Micro-Charts enables you to understand complex relationships in your data with sparklines. Sparklines are tiny charts with an intensity of visual distinctions comparable to words and letters invented by information design guru Edward Tufte. Placed in an Excel cell this format allows fast effective parallel comparisons......

2) SparkMaker : SparkMaker is an add-in for Microsoft Office that lets you create your own spark lines in Excel, Word, PowerPoint, and HTML documents....

3) Excel Flow-Breeze Flow Chart Software : 100% MS Excel integrated tool for making flow charts the fast easy way ... and it's a scalable Business Process Improvement Tool at a price you'll love ... Excel Flow-Breeze Flow Chart Software lets you Just Type the Text ... Excel Flow-Breeze Flow Chart Software converts your text into flowchart shapes ... adds flow lines ... formats the drawing automatically....

EXCEL TIPS AND TRICKS

I thought I would show you how we can use VBA to easily identify a table that has been filtered. By this I mean identify which field is using what criteria.

CUSTOM FUNCTION APPROACH

The 1st one we will use is a custom Excel function . This will not only flag the correct criteria field but also tell us what the criteria is. Let's assume the table to filter occupies A1:E1000 with A1:E1 being headings. First we should always have at least 3 rows above any table in Excel. Select rows 1:3 and go to Insert>Rows. This will insert 3 rows, or as many as you select. We will use at least 1 row to display the criteria in the appropriate column. Why at least 3 rows then? Good practice is the answer. This way we can set up criteria, if need be, for Advanced Filter .CRITERIA FUNCTION CODE

Below is the code that must be added to the Workbook, or an Excel Add-in . To add the code to a Workbook go to Tools>Macro>Visual Basic Editor (Alt+F11) then to Insert>Module and paste in the code below;

Function AutoFilter_Criteria(Header As Range) As String
Dim strCri1 As String, strCri2 As String

    Application.Volatile

    With Header.Parent.AutoFilter
        With .Filters(Header.Column - .Range.Column + 1)

            If Not .On Then Exit Function

                strCri1 = .Criteria1
            If .Operator = xlAnd Then
                strCri2 = " AND " & .Criteria2
            ElseIf .Operator = xlOr Then
                strCri2 = " OR " & .Criteria2
            End If

        End With
    End With
    
    AutoFilter_Criteria = _
     UCase(Header) & ": " & strCri1 & strCri2
End Function

Note the use of Application.Volatile in the code. This will ensure our function updates whenever the AutoFilter criteria changes. Ok, ensure you have AutoFilter applied to your table. If you don't, the function will return #VALUE! Now, in, say A1 Enter =AutoFilter_Criteria(A4) (A4 is the 1st heading) and copy across as many columns as you have headings. Now simply filter by any criteria and the function will display your criteria in the relative column cell.

COLOR CODE WITH CALCULATE EVENT

This one can be used in addition to the custom function above, or on its own. However, you really should have at least 1 volatile function on the Worksheet it is used in. To ensure this simply Enter =TODAY() in any cell. Right click on the Worksheets name tab, choose View Code and in here paste the exact code below.

Private Sub Worksheet_Calculate()
Dim lFilt As Long, lFiltArrows As Long
Dim lFiltRow As Long

    On Error Resume Next
    Application.EnableEvents = False
    lFiltRow = Me.AutoFilter.Range.Row
    lFiltArrows = Me.AutoFilter.Filters.Count
    
    Range(Cells(lFiltRow, 1), Cells(lFiltRow, _
        lFiltArrows)).Interior.ColorIndex = xlNone

        If Me.FilterMode = True Then
            For lFilt = 1 To lFiltArrows
                If Me.AutoFilter.Filters.Item(lFilt).On Then
                    Cells(lFiltRow, lFilt).Interior.ColorIndex = 46
                End If
            Next lFilt
        End If

    Application.EnableEvents = True
    On Error GoTo 0

End Sub

Come back to Excel and again filter your table. You will note that this one will automatically detect your headings which have AutoFilter applied. When no criteria is set, in others words not filtering, no color will change.

Until next month, keep excelling!

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

OzGrid Excel and VBA Newsletter November 2006

Newsletter Index

EXCEL TIPS AND TRICKS

EXCEL VBA TIPS AND TRICKS