VBA Needed - Change GANTT Bar Colour By Date RRS feed

  • Question

  • Project 2013: I want to change GANTT chart bar colours based on date to flag items which are late. Scenario as follows:

    I have a main task which runs from February 1st through March 1st. I have several indented deliverables listed under this main task with various dates they need to be completed by, e.g. delivery of a specific component. This component is scheduled due on February 1st but supplier tells me it will be 5 days late. When I change the finish date to 5th February I want the bar to automatically turn red as a visual indicator.

    Similarly, if the date for the main task slips by 7 days and becomes February 7th, when I change the date for this task I want any late tasks which would as a result now not be late to turn back to green.

    Is there a way to do this? (Basically driving sub-task bar colour by the main tasks start and/or finish date.

    Thanks in advance for any help with this.

    • Edited by CaelanT Monday, February 9, 2015 2:13 PM Change title to include "VBA Needed"
    Monday, February 9, 2015 12:47 PM

All replies

  • Hi Caelan,

    Before proposing more complex solution, I'd like to mention that in the "format tab", you can check the "late task" option to display with a dark grey color the late tasks in the Gantt chart.

    Note also that displaying the tracking Gantt, you can double click in the Gantt chart and change the late task format:

    Please tell us if  this solution is suitable for you, otherwise if you explain us why you need something else, we'll try to propose you a more convenient solution.

    Hope this helps,

    Guillaume Rouyre, MBA, MVP, P-Seller |

    Monday, February 9, 2015 12:59 PM
  • Don't believe this will help me.

    I cannot attach images yet because of account verification to show you a basic example.

    Effectively, if I have 100 sub-tasks under my main task, I want to change the bar colours of all sub-tasks based on changes in the start date of the main task. Initially only red and green bars. Red bars would immediately indicate sub-tasks which fall into the critical path based on any shift in the main task.

    Monday, February 9, 2015 1:23 PM
  • I'd like to understand a bit more, because it's like there is a loop in your need. Let me explain myself:

    • You want to set the color of sub-tasks based on the main task,
    • But the main task (assuming you are using automatic scheduling) dates are triggered by the sub tasks dates.

    Why not setting the sub task color only based on THEIR status?

    That being said, the principe is basically that you can color the task bars in the Gantt chart based on their own information (flag, custom fields...). If you need to go a step further and color the sub tasks based on the main task then it will require VBA code.

    Hope this helps,

    Guillaume Rouyre, MBA, MVP, P-Seller |

    Monday, February 9, 2015 1:31 PM
  • You are correct in your last sentence. I wish to colour the sub tasks automatically based on the main task.

    The main task is not driven by sub task dates.

    I want to track component manufacturing and delivery schedule vs. need by assembly schedule. If the assembly schedule shifts for any reason, I want the sub task colours to change. This negates the need to individually change each sub task if the main task dates change.

    • Edited by CaelanT Monday, February 9, 2015 1:40 PM
    Monday, February 9, 2015 1:36 PM
  • Then I'll let VBA colleagues jump in to help you, evenif I'm a bit doubtful about this manner of handling late tasks versus MS Project out-of-the-box features (baselines, deadlines, status date).

    Hope this helps,

    Guillaume Rouyre, MBA, MVP, P-Seller |

    Monday, February 9, 2015 1:42 PM
  • CaelanT,

    I'm one of the guys Guillaume refers to as "VBA colleagues". I'm having trouble understanding your statement, "the main task is not driven by the sub task dates". To me, and I'm sure anyone else who reads this post, a "main" task will be a summary line, and except for a manually scheduled summary line, the dates for a summary line are by definition determined by the start of the earliest subtask and the finish of the latest subtask.

    A screenshot of your example would be extremely helpful in understanding your need but if you have not yet been "authorized" to post an image, please detail in words the relationship of your "main" task and subtasks. Until we can understand that, we will be hard pressed to help you. I certainly don't want to spend time working on assumptions.


    Monday, February 9, 2015 4:21 PM
  • HI John,

    To explain in more detail, I have several build/assembly schedules (main tasks) with each having many deliverables (sub-tasks). Each main task has a start and finish date. All the sub tasks (design, manufacture, and delivery schedule) are required complete by the start date of the main task (or in some cases by a specific date within the timeframe of the main task). If any sub task is not complete on time there is the potential to stop/delay the main task.

    Because of other project activities which are not in my scope, the main task start and finish dates can be moving targets, changing frequently. The main task start and end dates are not driven by the sub tasks. Similarly none of the sub tasks are driven by any other sub task. None are linked as you would normally work in a schedule. The sub tasks themselves have independant schedules tracked elsewhere.

    So, the main and sub task dates will remain static unless I manually change them. What I want to do in Project is have red and green bars for each sub task which will automatically change relative to on time or late status. If any sub task changes to a late status, example being supplier will deliver 1 week late, when I change the sub task date I want the bar to turn red. Similarly if the main task dates move out by any number of days, when I manually change the main task dates I want all sub task bars to change colour based on the new on time/late status caused by the main task moving out.

    I hope this explains in more detail what I'm looking for. This would not be the main project schedule or tracker, but will be an upper level tool from which I will build on to instantly change status very visually for any sub task which could either move on or off the critical path.

    Tuesday, February 10, 2015 9:09 AM
  • CaelanT,

    From your description and particularly your last paragraph, this sounds like a presentation tool that would probably be better created in Excel or PowerPoint, not Project. Is there a reason you want to use Project for a non-schedule presentation tool?

    I'm still have a hard time envisioning how it would look and rather than me guessing about the format, (I just don't have the time), hopefully you are "cleared" to post an image by now.

    Short of that, you could activate different bar styles by setting or clearing a task field each time you do a manual update. If a baseline is set for the initial state, you could probably use a custom formula to set the flag for you, so it would be automated.


    Tuesday, February 10, 2015 4:33 PM
  • John,

    This would be schedule based, but this element would not be set up in the way you would normally expect. This isn't to create a presentation tool, but rather to have changes visible for myself and the rest of the project team across hundreds, sometimes thousands of product critical areas.

    There will be elements in the project which will be schedule based in the normal sense, and I would set up everything in the one project.

    I still cannot post images.


    Thursday, February 12, 2015 11:49 AM
  • CaelanT,

    It seems strange that you are still not "validated" to post images. In a case like yours, that's a real handicap (for us). Tell you what, if you have a mockup of your Project file, you can e-mail it to me and we can go from there. However, my mail server cannot accept attachments larger than 1M so if it's bigger than that when zipped we'll have to find another way. My address is below. I will ask some questions.

    If you do send, please include a detailed description of what you want the file to look like and any other notes of explanation even if you think you've already explained above.



    (remove obvious redundancies and the 7th character is a letter)

    Thursday, February 12, 2015 3:29 PM