Conditional Formatting for rows of empty cells corresponding with dates between

[avril18]

Good Afternoon:

I am attempting to build a visual timeline and am purposefully avoiding using a Gantt table- instead I want to be able to input dates under "start date" and "end date", and empty cells to the right in the same row will automatically fill with color corresponding to the date columns (to include both start/end dates and between). The data from weeks/dates/day of week cells at the top do not extend beyond the cells that contain the date information. It seems like Highlight Cell Rules might apply, but I am not sure how to make the start and end dates implicitly trigger the empty cells according to where they land under the top date sections/columns.

I have attached a snip of the sheet I am attempting to fill, with manually filled rows for what I am looking for it to ultimately look like. The 1st/current sheet starts on February 19, 2024 and extends to September 30, 2024 for a Fiscal Year. I will be duplicating the solution onto sheets that range between October 1, 2024 to September 30, 2025, then again for the same range but between 2025 to 2026, etc. etc. The dates boxes a at the top will always start on column "I" (capital i) of the sheet's x axis.

Said differently: if I change the start date cell in column F to say February 26, 2024, and the end date to March 15, 2024 in column G of a particular row, I want the corresponding empty cells below the associated dates to automatically fill (like the first green row below). If I end up changing the dates, I want whatever new dates (and therefore cells) to instead be filled automatically.

Let me know how else I might articulate this to achieve understanding.

 

[DanteAmor]

Hi and welcome to MrExcel.

Check this:

Excel conditional formatting in a Gantt Chart

I was trying to provide conditional formatting in my Gantt Chart. However, I have difficulty developing a formula that will trigger to highlight the cell or range, as the date is not a usual one. I'm hoping that someone from this group can help me solve the issue. I manually provided the colors...

www.mrexcel.com

 

[avril18]

Hi and welcome to MrExcel.

Check this:

Excel conditional formatting in a Gantt Chart

I was trying to provide conditional formatting in my Gantt Chart. However, I have difficulty developing a formula that will trigger to highlight the cell or range, as the date is not a usual one. I'm hoping that someone from this group can help me solve the issue. I manually provided the colors...

www.mrexcel.com

This is excellent, I altered to apply to my sheet according to the cell values and it works perfectly.

 

[avril18]

This is excellent, I altered to apply to my sheet according to the cell values and it works perfectly.

I also applied the formula to cover the entire sheet of dates, which ranged from I5 to IE433 (the last row/column). I utilized this formula adapted from your reference:

=OR(AND($G8<>"",I$5>=$F8,I$5<=$G8),AND($G8<>"",$F8>=I$5,$F8<J$5))

and adapted the "applies to" to the following:

=$I$8:$IE$433

THANK YOU.

 

[avril18]

I also applied the formula to cover the entire sheet of dates, which ranged from I5 to IE433 (the last row/column). I utilized this formula adapted from your reference:

=OR(AND($G8<>"",I$5>=$F8,I$5<=$G8),AND($G8<>"",$F8>=I$5,$F8<J$5))

and adapted the "applies to" to the following:

=$I$8:$IE$433

THANK YOU.

For reference for others reviewing this page re: this solution, the actual dates of the week land on row 5, and the cells being filled begin on row 8. Since it is not included in the initial screenshot above.