Dynamic Project Planner (Gantt Chart) in Excel

Here we have created a beautiful and dynamic Project Planer (Gantt chart) in Excel. We have given a drop-down to change the weekly and daily view. There are 3 type of status has been given Pending, Completed and In-Progress. We have used VBA to change the View daily to weekly and visa-versa. This is password protect worksheet. The password is “1234”

Below is the Weekly View-

Below is the daily View:

VBA Code which we have used to change the view-

Option Explicit

Sub Refresh_Data()

Application.ScreenUpdating = False

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Project_Plan")




sh.Unprotect "1234"




Dim i As Long




sh.Range("G3:XFD3").UnMerge

sh.Range("G1:XFD3").Clear

sh.Range("G1:XFD3").Orientation = 0


Dim lc, lr As Integer

For i = Application.WorksheetFunction.Min(sh.Range("C:C")) To Application.WorksheetFunction.Max(sh.Range("D:D"))

    If sh.Range("G1").Value = "" Then

        sh.Range("G1").Value = i   

    Else

        lc = sh.Range("XFD1").End(xlToLeft).Column

        sh.Cells(1, lc + 1).Value = i

    End If

Next i

lc = sh.Range("XFD1").End(xlToLeft).Column

If sh.Range("C1").Value = "Daily" Then

   sh.Range("G3").Value = "=G1"

   sh.Range("G3", sh.Cells(3, lc)).FillRight
  

   sh.Range("E3").Copy

   sh.Range("G3", sh.Cells(3, lc)).PasteSpecial xlPasteFormats

   sh.Range("G3", sh.Cells(3, lc)).NumberFormat = "D-MMM"

   sh.Range("G3", sh.Cells(3, lc)).Orientation = 90

   sh.Range("G3", sh.Cells(3, lc)).EntireColumn.ColumnWidth = 2.5

  

Else

      For i = 7 To lc Step 7

        sh.Cells(3, i).Value = "Week-" & i / 7

        sh.Range("E3").Copy

        sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).PasteSpecial xlPasteFormats

        sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).EntireColumn.ColumnWidth = 0.8

        sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).Merge

        sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).HorizontalAlignment = xlCenter

        sh.Range(sh.Cells(3, i), sh.Cells(3, i + 6)).VerticalAlignment = xlCenter

   Next i

      lc = sh.Range("XFD3").End(xlToLeft).Column + 6

End If

lr = sh.Range("B" & Application.Rows.Count).End(xlUp).Row

sh.Range("G1:XFD1").NumberFormat = "D-MMM-YY"

sh.Range("G1:XFD1").Font.Color = VBA.vbWhite

sh.Range("H4:XFD" & Application.Rows.Count).Clear

sh.Range("G5:G" & Application.Rows.Count).Clear

sh.Range("A" & lr + 1, "A" & Application.Rows.Count).EntireRow.Clear

sh.Range("B:F").Locked = False

sh.Range("G1:XFD3").Locked = True

sh.Range("G1:XFD3").FormulaHidden = True

sh.Range("G4:G" & sh.Range("B" & Application.Rows.Count).End(xlUp).Row).FillDown

sh.Range("G4", sh.Cells(lr, lc)).FillRight

With sh.Range("B3", sh.Cells(lr, lc))

    .Borders(xlEdgeBottom).LineStyle = xlDouble

    .Borders(xlEdgeBottom).Color = vbBlack




    .Borders(xlEdgeLeft).LineStyle = xlDouble

    .Borders(xlEdgeLeft).Color = vbBlack

   

    .Borders(xlEdgeRight).LineStyle = xlDouble

    .Borders(xlEdgeRight).Color = vbBlack

   

    .Borders(xlEdgeTop).LineStyle = xlDouble

    .Borders(xlEdgeTop).Color = vbBlack

End With

sh.Protect "1234"

End Sub 

$1.99 – Purchase Checkout Added to cart

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@PKAnExcelExpert

Watch the step by step tutorial for Dynamic Project Planner: