Author: Oscar Cronquist Article last updated on July 27, 2017

Bryan asks:

i am trying to do a conditional formatting for a calendar row. (essentially, a gant chart)

i have a schedule table with event in 1st column, start date in 2nd column, end date in 3rd column, and a 2-criteria condition for the event in the 4th column.

how do i condition format the cell row beneath the calendar row so that they are within the start/end date and will be color coded brown or blue based on the 2-criteria? I already have a conditional formatting done for weekends and holidays for the calendar row itself, so hence the new row beneath.

I have a schedule table with 4 columns.
1. Column D has the event name
2. Column E has the Start date
3. Column F has the End date
4. Column G has a drop-down choice of "home" or "out of area"

I have a calendar going from I1:IV1. I have it setup for highlighting federal holidays and weekends for conditional formatting.

I have a row setup beneath the calendar from I3:IV3 that I'd like to conditionally format to reflect based on the start-end date ranges in the schedule table with color coding for "home" or "out of area".

Answer:

Conditional formatting formula "Brown"

=SUMPRODUCT(--(\$E4<=\$I\$1:\$R\$1),--(\$F4>=\$I\$1:\$R\$1))*(\$G4="Home")

Conditional formatting formula "Blue"

=SUMPRODUCT(--(\$E4<=\$I\$1:\$R\$1),--(\$F4>=\$I\$1:\$R\$1))*(\$G4="Out of area")

How to create an conditional formatting formula (excel 2007 and above)

1. Go to tab "Home"
2. Click "Conditional formatting" button
3. Click "New Rule..."
4. Click "Use a formula to determine which cells to format"
5. Click in "Format values where this formula is true" field
6. Type formula
7. Click "Format..." button
8. Go to tab "Fill"
9. Pick a color
10. Click OK
11. Click OK

Download excel *.xlsx file

Conditional format events overlapping federal holidays.xlsx