Conditional format for Excel timeline/gantt chart

[MelanieB]

Someone here gave me a link to show me how to create a timeline/gantt chart in Excel. Love it! Awesome.
I set it up with slicers to filter the timeline. Boss was very impressed.. but of course, now he wants me to color code the bars in the chart based on an effort Level assigned to each project. This is value is in the data table I used to create the chart.

My question is how to color the chart based on the level? I keep thinking conditional formatting, but I am not sure how to do it with a chart. Do you have a favorite site, tutorial or video you can point me to so I can do this? I did google it, and came up with a bazillion options and many of them are very old. I'm hoping you can save me the time of having to watch a bunch of them and point me to one you have used and like?



Thanks!

[HansV]

Charts don't have conditional formatting. You may have to use VBA, but without having a clear idea of what you want to accomplish it's impossible to provide more specific help...

[Stefan_Sand]

Hello, Hans made a VBA solution for a Gantt with conditional colors for the task stats for me in 2017.
Please see the link if this might be a solution for You,
best regards,
Stef

https://eileenslounge.com/viewtopic.php ... ht#p211245

[MelanieB]

I attached a screen shot. Each project is assigned an OCM level (1, 2, 3). They want me to color the bar in the chart depending on what OCM level it is. Right now we only have a few, but this could be 30+ projects so they want something more visual that doesn't require the filter/slicer. But, they do like the slicer, so we will keep it.

Does this help? I can send you a cop of the data table if that helps.

[HansV]

It would be nice to have a sample workbook (without sensitive data)

[MelanieB]

Here is the scrubbed workbook. Thanks for taking a look at it.

[HansV]

See the attached version. I added a macro

Code: Select all

Sub ColorLevels()
    Dim cht As Chart
    Dim ser As Series
    Dim pnt As Point
    Dim rng As Range
    Dim i As Long
    Set rng = Worksheets("ProjDataTbl").ListObjects("Projects").ListColumns("OCM Level").DataBodyRange
    Set cht = Worksheets("ProjVisual").ChartObjects("Chart 1").Chart
    Set ser = cht.SeriesCollection(2)
    For i = 1 To rng.Count
        Set pnt = ser.Points(i)
        Select Case rng(i).Value
            Case 1
                pnt.Format.Fill.ForeColor.RGB = RGB(128, 0, 0)
            Case 2
                pnt.Format.Fill.ForeColor.RGB = RGB(0, 128, 0)
            Case 3
                pnt.Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
        End Select
    Next i
End Sub

and assigned it to a Forms command button on the second sheet. You can change the colors in the macro, of course.

OCM-PMO Dashboard (1).zip

[MelanieB]

Beautiful! Thank you so much.