Automatically change cell color based on other cells' date range

Question

Hi, I'm working on a Gantt chart for a client, and they want to know if there's a way to automatically change a cell's color based on a date range. I think I may be able to do this with conditional formatting, but I can't seem to get the formula right. Can anyone help?
Here's a screenshot of a sample spreadsheet:

The idea is that the cells under the year/month columns will change color to reflect the start and end dates in the row. So for the one I manually entered above, since the project runs from October to June, the cells representing Oct through June are a deeper color of yellow.
Is there a way to automate this? Hoping I've explained it clearly!
Thanks to any/all for their help!
-c

GarthRaiziel · Accepted Answer

Hi, christie_c,
 
I had to make a change to the way you're listing the months in order to create a solution for you. Currently, the months are just strings and cannot easily be compared to the dates in E and F. In the 'GarthR Solution' sheet I updated each month to a date and then formatted it as a 3-character month name. For example, the value in cell I10 is 1/1/24, but is formatted to display "Jan." This allows you to compare a date in E or F to it.
As for the conditional formatting rule:
Apply To Range: I12:BP18
Format Cells If: Custom formula
Custom Formula Is: =AND(DATE(YEAR($E12),MONTH($E12),1)<=I$10,DATE(YEAR($F12),MONTH($F12),1)>=I$10)
Formatting Style: Dealer's choice
Unfortunately, this only allows for a single color to be selected, as opposed to the darker yellow and dark grey in your example. Using multiple colors in conditional formatting will require multiple rules. If you want to use those two separate colors, let me know and I can adjust the 'GarthR Solution' sheet.
 
Hope this helps!
--
When you've received a response that answers your question, please observe these forum courtesies:
 • Leave your demo sheet shared as part of this solution's archive
 • Click 'Recommend' on the reply that best addressed your question
 • Post again soon!