VBA: Gantt chart coloring

[HansDK]

Hi all. I am trying to create a dynamic gantt chart in excel through VBA. I am currently struggling with the offsetting the coloring part.

Below, you can see my code which I have produced so far. I am having problems with the final bit. I hope someone will be able to help me.

BR Hans.

Sub gantt()
Application.ScreenUpdating = False
    
    Dim duration As Range
    Dim DateColumn As Range
  
    Dim h As Range
    Dim i As Range
    Dim j As Range
    Dim D1 As Long
    Dim D2 As Range
  
    Set DateColumn = Worksheets("Tender Schedule").Range("I11:I98") 'users will define day, month and year. DateColumn is used to put the date together. The date from DateColumn will be used as a starting point for the coloring
    Set duration = Worksheets("Tender Schedule").Range("H11:H98") 'duration is the expected duration which will define the length of the colored part.
  
    D1 = Worksheets("Tender Schedule").Range("M2") - 1 'D1 is the overall start of the project
  
    For Each h In DateColumn
        h.Value = Format(h.Offset(0, -4) & "-" & h.Offset(0, -3) & "-" & h.Offset(0, -2), "dd/mm/yyyy") ' This collects the date from the 3 cells to the left.
    Next h
  
  
    For Each D2 In DateColumn
    For Each i In duration
    If Int(i.Value) > 0 Then 'There are blank cells in the date column, as there are different sections of the Gantt chart. Therefore, not all cells should contain information
            D2 = CLng(VBA.DateValue(i.Offset(0, 1))) ' This collect "date 2" which is fed to DateColumn. This will be the beginning of a specific event/part project.
            'Debug.Print (D2)
            'Debug.Print (DateValue(D2) - DateValue(D1))
            i.Offset(0, (D2 - D1) + 1).Resize(0, i.Value).Interior.Color = vbGreen 'this should offset from the duration column and color the relevant part of the Gantt chart.
    End If
    Next i
    Next D2

End Sub

 

[Jeffrey Mahoney]

Have you considered using Conditional Formatting instead of macros to color the cells?

Primavera P6 Progress Line