Conditional formatting that will reference entire table, not just first row.

I have attached an example spreadsheet for reference:

I am essentially trying to create a gantt chart within the cells where table 1 corresponds to row 3 and table 2 to row 4. I want the conditional formatting to reference all rows in a table instead of just the first. My conditional formatting formula is currently:

=AND($AP3="Greenbrier",AND(B$2>=$AN3,B$2<=$AO3))

where it is searching for "Greenbirier" text in the AP column, and coloring the range defined by columns AN and AO appropriately.

As of now, creating the chart would require me to have way too many rules (12 areas*14 rows in table)

ex)

=AND($AP3="Greenbrier",AND(B$2>=$AN3,B$2<=$AO3))

=AND($AP4="Greenbrier",AND(B$2>=$AN4,B$2<=$AO4))

=AND($AP5="Greenbrier",AND(B$2>=$AN5,B$2<=$AO5)) ....etc


I have been trying to create a formula that would reference the entire table, something like:

=AND($AP$3:$AP$16="Ohio",AND(B$2>=$AN$3:$AN$16,B$2<=$AO$3:$AO$16))

which would enable me to have 12 rules per table, but have had no luck.

I welcome any ideas regarding ways to re-write my formula to make this happen.

Thanks!

Thanks NBVC,

I think that changing the "applies to" ranges would apply the formatting to that range, but still wouldn't be referencing the table range that I want. For table one, that range would be AM3:AP16.

Unfortunately, I am not. I am guessing that you selected row 3 within the date range and dragged it down. What I need is all of the data in table 1 to plot on row3, and all of the data on table 2 to plot on row 4. I have colored in row 4 to represent how I want it to format. Eventually, I am going to have about 14 tables that will be changing weekly, so I want this process to be automated. I have been experimenting with index and vlookup functions, but still no dice.

Still hunting for a solution

Thanks NBVC,

Are you able to attach the file containing the functioning work that you did?

NBVC,

Please disregard my previous post, I have gotten it to work. So far, this appears to function way better than I had anticipated, with far less rules than expected. I thought I would need at least 12 per table, but now it looks like just 12 for the whole workbook. As of now, I can declare this issue solved.

Thanks a lot!