Excel VBA Gantt Chart: Build and Automate a Timeline

Yes. You can use VBA to build a Gantt-style schedule in Excel by turning task start dates and durations into a calendar grid, then applying conditional formatting to show each task as a colored bar. The macro below creates that grid from a simple task list; it is a worksheet-based Gantt view, not a separate chart object.

What an Excel VBA Gantt chart is

A Gantt chart displays tasks against time: each row represents a task, and a horizontal bar shows when the task starts and finishes. In Excel, “VBA Gantt chart” can mean either a bar chart made from a stacked bar chart object or a calendar grid built from cells. The grid method is often easier to read for day-by-day plans and simpler to customize with VBA.

VBA is useful when the schedule needs to be rebuilt repeatedly, such as after importing a task list or changing dates. A macro can add date headings, calculate finish dates, and create the visual bars in one run. It does not make the schedule automatically reliable: the task data still needs to be accurate, and the macro must be run again when the plan changes if the display is generated as a snapshot.

Prepare the task list

In a new worksheet in Excel for Microsoft 365 on Windows, set up these headings in row 1:

A1: Task
B1: Start
C1: Duration
D1: Finish

Enter one task per row, starting in row 2. Use real Excel dates in column B and positive whole-number durations in column C. The macro treats duration as calendar days and counts the start date as day one. For example, a task starting on March 3 with a duration of five days finishes on March 7, not March 8.

With the start date in B2 and a five-day duration in C2, the inclusive finish-date formula is =B2+C2-1. The result for March 3, 2026, and a duration of 5 is March 7, 2026. Format column B and D as dates so the serial numbers Excel stores are displayed clearly. This example assumes consecutive, complete task rows; the macro checks that the start dates and durations are usable.

How the cell-grid method works

The schedule area begins in column E. Each heading across row 1 is one calendar day, from the earliest task start to the latest task finish. A conditional-formatting rule colors a task’s row when the date at the top of a column falls between that task’s start and finish. Because the rule refers to the cells in each row, it draws a continuous bar across the task’s active dates.

This layout is an ordinary worksheet range rather than an embedded Excel chart. That makes it straightforward to label, print, filter, and format, but it also means the daily timeline can become wide for a long project. For a multi-year plan, consider a weekly or monthly view, or use a stacked bar chart with a coarser time scale.

Add and run the VBA macro

Save a copy of the workbook before running any macro: VBA changes cannot be reversed with Ctrl+Z. Save the working file as an Excel Macro-Enabled Workbook (.xlsm). In desktop Excel for Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code below into the module. Return to Excel, press Alt+F8, select BuildGanttGrid, and choose Run. The macro uses the active worksheet, so activate the sheet containing the task list first.

Option Explicit

Sub BuildGanttGrid()
    ' Builds a daily, conditional-formatting Gantt grid on the active sheet.
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    Dim clearLastCol As Long
    Dim firstCol As Long
    Dim taskRow As Long
    Dim dayCol As Long
    Dim taskDays As Long
    Dim startDate As Date
    Dim finishDate As Date
    Dim minStart As Date
    Dim maxFinish As Date
    Dim barRange As Range
    Dim barRule As FormatCondition

    Set ws = ActiveSheet
    firstCol = 5
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "Enter at least one task in row 2 or below."
        Exit Sub
    End If

    ' Validate rows and find the full date span before changing the grid.
    For taskRow = 2 To lastRow
        If Len(ws.Cells(taskRow, "A").Value) = 0 Then
            MsgBox "Every task row needs a task name."
            Exit Sub
        End If
        If Not IsDate(ws.Cells(taskRow, "B").Value) Then
            MsgBox "Enter a valid start date in every task row."
            Exit Sub
        End If
        If Not IsNumeric(ws.Cells(taskRow, "C").Value) Then
            MsgBox "Enter a positive whole-number duration in every task row."
            Exit Sub
        End If
        If CDbl(ws.Cells(taskRow, "C").Value) <= 0 Then
            MsgBox "Durations must be positive whole numbers."
            Exit Sub
        End If
        If CDbl(ws.Cells(taskRow, "C").Value) <> Fix(CDbl(ws.Cells(taskRow, "C").Value)) Then
            MsgBox "Durations must be positive whole numbers."
            Exit Sub
        End If

        startDate = CDate(ws.Cells(taskRow, "B").Value)
        taskDays = CLng(ws.Cells(taskRow, "C").Value)
        finishDate = startDate + taskDays - 1

        If taskRow = 2 Then
            minStart = startDate
            maxFinish = finishDate
        Else
            If startDate < minStart Then minStart = startDate
            If finishDate > maxFinish Then maxFinish = finishDate
        End If
    Next taskRow

    lastCol = firstCol + CLng(maxFinish - minStart)
    If lastCol > ws.Columns.Count Then
        MsgBox "The daily timeline is too wide for the worksheet."
        Exit Sub
    End If

    ' The schedule area from column E onward is reserved for this macro.
    clearLastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    If clearLastCol < firstCol Then clearLastCol = firstCol
    If clearLastCol < lastCol Then clearLastCol = lastCol
    ws.Range(ws.Cells(1, firstCol), ws.Cells(lastRow, clearLastCol)).ClearContents
    ws.Range(ws.Cells(1, firstCol), ws.Cells(lastRow, clearLastCol)).Interior.Pattern = xlNone

    For taskRow = 2 To lastRow
        ws.Cells(taskRow, "D").Formula = "=B" & taskRow & "+C" & taskRow & "-1"
        ws.Cells(taskRow, "D").NumberFormat = "d-mmm-yyyy"
    Next taskRow

    For dayCol = firstCol To lastCol
        ws.Cells(1, dayCol).Value = minStart + dayCol - firstCol
        ws.Cells(1, dayCol).NumberFormat = "d-mmm"
        ws.Cells(1, dayCol).Font.Bold = True
    Next dayCol

    Set barRange = ws.Range(ws.Cells(2, firstCol), ws.Cells(lastRow, lastCol))
    barRange.FormatConditions.Delete
    Set barRule = barRange.FormatConditions.Add( _
        Type:=xlExpression, _
        Formula1:="=AND(E$1>=$B2,E$1<=$D2)")
    barRule.Interior.Color = RGB(68, 114, 196)

    ws.Columns("A:D").AutoFit
    ws.Range(ws.Cells(1, firstCol), ws.Cells(1, lastCol)).EntireColumn.ColumnWidth = 4
End Sub

The macro writes a finish-date formula in column D, generates the daily headings, and applies a blue conditional-formatting bar. Its schedule area starts at E and is reserved for the macro: rerunning it clears contents and fill colors from that area for the rows it processes. Keep unrelated notes and data out of those cells. The code expects task rows to be contiguous, with a name, start date, and duration in every row through the last task in column A.

Change the appearance and time scale

After the macro runs, you can adjust the bar color by changing the RGB values in the code. For example, RGB(68, 114, 196) specifies the blue fill. You can change the date format in the header line to show a longer label, but wider columns may make the schedule harder to print. Freeze the task-name columns or the top row from View > Freeze Panes if you need to scroll through a wide timeline while keeping labels visible.

Daily headings are useful for short projects, but not every plan needs that level of detail. A weekly timeline can reduce the number of columns, though the bar logic then needs to compare week ranges with task ranges rather than treating each column as one date. Another option is to create a stacked bar chart: use the start date as an invisible offset series and duration as the visible series, then format the horizontal axis as dates. That is a true chart object and can be a better fit for presentations, while a cell grid is often easier to maintain as a working schedule.

Calendar days, workdays, and dependencies

The sample counts calendar days, including weekends. This is a deliberate simplification, not a claim that a five-day task always takes five working days. If a project uses working-day durations, decide how weekends and holidays should be handled before changing the calculation. Excel has functions such as WORKDAY and NETWORKDAYS for workday date calculations, but the timeline headings and the duration interpretation must also follow the same calendar rules. Public holidays require a clearly maintained holiday-date list if they are to be excluded.

A basic Gantt bar also does not represent dependencies, resource availability, or completion percentage. A task can overlap another task even if a real plan would not allow it. To show progress, add a separate percent-complete field and use a second visual treatment; to show dependencies, add predecessor identifiers and additional logic. These features require more than drawing a bar between two dates, so verify the schedule rules rather than assuming that the picture itself validates the plan.

Common problems and safe macro use

No bars appear: Check that B contains Excel dates, C contains positive whole numbers, and the macro is running on the correct active sheet. Text that looks like a date may not be recognized as a date value.
The timeline is unexpectedly long: The macro includes every calendar day between the earliest start and latest finish. A task far in the future expands the whole date axis.
Old dates or colors remain: The macro clears the reserved schedule area only through the last used heading in row 1 and the current task rows. Keep that region dedicated to the generated schedule.
The macro will not run: Confirm the file is saved as .xlsm and that macros are permitted by your organization’s security policy. Do not enable all macros globally just to run one workbook; use trusted files and follow your organization’s guidance.

VBA runs in desktop Excel, including Excel for Mac, though the editor and keyboard shortcuts differ on Mac and some Windows-specific automation or file paths may need changes. Excel for the web can open a macro-enabled workbook but cannot create, edit, or run VBA macros. The grid itself can still be viewed there, subject to the workbook’s supported features.

Cost and alternatives

VBA and worksheet formatting are included with desktop Excel; this method does not require a paid chart add-in. The practical cost is maintenance: someone must understand the layout, protect the task data from accidental edits, and update the macro if the project rules change. If several people need simultaneous editing, approvals, notifications, or portfolio reporting, a dedicated project-management application may be more suitable than a macro-enabled workbook.

For a small plan that changes occasionally, manual conditional formatting or a prebuilt workbook template may be enough. For a polished chart in a report, a stacked bar chart may be easier to resize and export. Choose VBA when repeatability matters and the team is comfortable managing macro security; choose a simpler worksheet or another planning tool when macros are blocked or the schedule needs broader collaboration features.

Frequently Asked Questions

Can Excel make a Gantt chart without VBA?
Yes. You can create a cell-based timeline with conditional formatting or build a stacked bar chart manually. VBA is optional; it automates repetitive setup and refresh work rather than providing a feature that Excel otherwise lacks.

Does this macro count weekends?
Yes. It treats every calendar date as one day, including Saturday and Sunday. For a working-day schedule, revise both the finish-date calculation and the date-axis logic to account for weekends and any relevant holidays.

Can I run VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Use desktop Excel for Windows or Mac to run the macro; the resulting worksheet grid can generally be viewed in the web app.

Why use a cell grid instead of a bar chart?
A grid places task labels beside daily dates and is easy to format as a working schedule. A stacked bar chart is a separate chart object and may be easier to present, but it takes care to format the date axis and hide the offset series.

Can I undo a macro with Ctrl+Z?
No. Running VBA clears and changes worksheet cells outside Excel’s normal undo history. Save a separate copy before running the macro, especially if the reserved area may contain information you need.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.