Trying to change Gantt chart in Google Sheets to highlight a cell based on date range and progress percentage

Question

I have been working on a dynamic Gantt chart template in Google Sheets for awhile now. Here's how it currently looks:

As you can see, the task bar currently highlights gray dependent on the start and end dates of the task using the formula =AND(I$4>=$E6,I$4<=$G6).

In non-cell specific terms: =AND(todaysdate$cell>=$startdatecell,todaysdate$cell<=$enddatecell)

I want to keep that function, but I want to add an aspect that will change that bar color dependent on if the progress percentage is 100%. (Essentially a visual way to show that task is complete vs. just scheduled.

Right now, all I can get is for the cells to change color up to those gray bars or after those gray bars, but I can't get the color to change in the gray bar itself. Any ideas on how I need to change my function to add this capability?

Answer 1

Update: I have found the answer. I split the above formula into two conditions as such:

=AND($C6=100%,I$4>=$E6,I$4<=$G6) then dark gray
=AND($C6<100%,I$4>=$E6,I$4<=$G6) then light gray

In non-cell specific terms: =AND($progresscell=value%,todaysdate$cell>=$startdatecell,todaysdate$cell<=$enddatecell) - shows completed task =AND($progresscell<value%,todaysdate$cell>=$startdatecell,todaysdate$cell<=$enddatecell) - shows scheduled but incomplete task