Simple Gantt Chart Conditional Formatting (New Column)

[hb_helt]

Hello everyone,

I am using the Simple Gantt chart template from Vertex42 for work. My boss is requesting I add a new column in "G" for a "completed date". Say the task was completed 1 or 2 days before the predicted end date (F column, that I will rename "due date") this column would reflect that. Now, my question is how do I condition the bars under the calendar to turn green when a value is entered into the new "completed date" column in G. Also, is there a way I could turn the bars red if the current date is past the "due date" (end date, F column)? Thank you everyone in advance!

[Shareez Saleem]

Amend the first formula like this
=AND($F5="",G$3>=$D5,G$3<=$E5)

Add a new conditional formatting
=AND($F5<>"",G$3>=$D5,G$3<=$F5)

F5= Completed Date
E5=Due Date
D5=Start Date

G3= Calendar date in the Gantt chart (start date)

Attached sheet FYR.

[hb_helt]

Thank you so much for taking time to reply! Would it matter that on my chart my start date = E9, my Due date =F9, and my new completed date column will be in G9? My calendar start date is also in I5. Will this make a difference to the formulas you have provided? Again thanks for the help!

[hb_helt]

I've tried attaching the excel file itself if that is any help!

[JeteMc]

Perhaps the following will help.
1. For Green: =AND(task_start<=J$5,task_end>=J$5,$G7<>"")
2. For Red: =AND(task_start<=J$5,task_end>=J$5,$G7="",task_end< TODAY(),$D7<1)
3. For Gray: =AND(task_start<=J$5,task_end>=J$5)
Note that in the attached file the formulas in E3 and E10 are changed to illustrate how the conditional formatting rules work.
Let us know if you have any questions.

[NavyChief]

Good morning All,
I tried to use VBA to count how many cells with color on each column but no luck. The VBA won't work with the conditional formatting formula used to color the row. Thank you.

[JeteMc]

Administrative Note:

Hello NavyChief and Welcome to Excel Forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Let us know if you have any questions.