Back to Excel Newsletter Archives
EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS
Got any Excel Questions? Free Excel Help
This month, due to time restraints, I'm going to show you an idea for the creation of Gantt charts, also known as timelines,
from Andy Pope. It involves the use of: VLOOKUP, Conditional Formatting, INDEX and MATCH .
Andy answered a question about the creation of Gantt charts in the OzGrid free Excel question and answer forum .
The question and answer can be seen here . There is also a download in post 4 from Andy
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!
Got any Excel Questions? Free Excel Help
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!
Software Categories Search Software
Excel Add-ins || Excel Training || Excel Templates || Employee Scheduling Software || Excel Password Recovery and Access & Word etc|| Excel Recovery and Access & Word etc || Financial Software || Financial Calculators || Conversion Software || Construction Estimating Software || Drilling Software || Real Estate Investment Software || Time Management Software || Database Software || Neural Network Software || Trading Software || Charting Software || Windows & Internet Software || Barcodes Fonts, ActiveX, Labels and DLL's || Code Printing Software || Outlook Add-ins
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.