Gantt Charts In Excel. Color AutoFilter Criteria Fields

This month, due to time restraints, I'm going to show you an idea for the creation of Gantt charts, also known as timelines,

If registering on the forum, please do read the all the rules though as we enforce them with vigor

If visiting the forum and have any questions regarding this, or any other problem, DO NOT REQUEST HELP DIRECTLY FROM ANDY!!!

You are free to post questions, ***but asking for Andy will not be tolerated***. He is a busy man and answers questions on the OzGrid free Excel question and answer forum most days. Abuse of his kind nature will result is permanent ban. Also, by posting a question please do NOT assume it WILL be answered.

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 StringDim 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 & strCri2End 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 LongDim 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 0End 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