Conditional Formatting Based on Date Range - Gantt

Hi Everyone,

I'm trying to color fill in cells based on the date ranges in other cells. For example, A3 has a date of "11/9/2017". B1 reads "11/3/2017," C1 reads "11/10/2017," and D1 reads "11/17/2017." I want C3 to fill based on the fact that 11/3/2017 < 11/9/2017 <= 11/10/2017. Does this make sense? My overall purpose is to create formula that auto fills across an entire sheet based on whether the dates in Column A fall within a week dictated in Row 2. Visual representation of what I'm trying to achieve is attached. Thanks for anyone who can give me some direction here.

Try this:

Highlight B2:G5 > Conditional Formatting > New Rule > Use a formula

=MIN(IF($B$1:$G$1>=$A2,$B$1:$G$1))=B$1

Format: Fill yellow > OK > OK

Just make sure to correct the dates that you have in F1:G1 assuming that was a typo.

Hi 63Faclondude, thanks for the advice, but it doesn't quite get me there. The dates aren't typo'd. The order of dates in column A may not be sequential or weekly. Row 1 will always be sequential and weekly. What I'm trying to achieve is what the "After" table in my attachment looks like. Right now the formuula you provided fills in row 2 perfectly, but then proceeds to fill in the rest of the table, which I do not want. Any further advice is appreciated again. Thanks so much.

I'm afraid that I do not understand what you're looking to do.

I can create a Conditional Formatting formula to replicate the fill color that is in B8:G11 (this is what I did in post #2 but with B2:G5).

Why does 11/25/2017 change to 12/1/2017 and 11/26/2017 change to 12/8/2017? All of the other dates are the same in the BEFORE and AFTER tables.

Thanks again for your help. Sorry for any typos and my bad explanation. Attached is a new example that I hope better represents what I need.
In column A, I'm tracking celebrity birthdays, and they will both repeat and be out of sequential order. In Row 2, I'm tracking dates which represents the end of the week. I want conditional formatting that fills in the blank cell if the date in column A falls within the week ending in Row 2.
I know it's a weird request and a strange way to organize information, but in my mind it's "Fill in cell C4 with a color if A4 is between B2 and C2.
My attachment represents what I want to achieve once I have the conditional formatting correct.

Hi 63falcondude,

Thanks again, however it didn't quite work. I get an error message. Sorry if this is a stumper. I'm at a loss as to what to do.
Error Message.JPG

I'm not sure where it is going wrong for you.

Using your workbook from post #5, and following the instructions from post #6 produces the expected results (B3, C4, G5, and G6 filled in red).

See attachment.

I was able to replicate it finally, thank you! For some reason after running the formula once, the cells in the formula would change. Once I went back in and corrected them, this worked. This one is solved. Thank you!!!!!!!!!

You're welcome. Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.