Formula to change the color of a task name or date cell, based on another date cell

Charmainem3

Hi all! Theoretically, I'd like to state: If the cabling will be delivered, but it won't arrive within 14 days before the date of installation, then change cell color of these tasks below to indicate the need to order temporary/alternate cabling solution.

I realize it may be too dynamic to only be set-up in conditional formatting, so i've been trying to write the best formula that will say: if Date in cell @row, is less than 14days of date in cell row20, then highlight rows 4,5,6 in yellow. Also, would I be correct in writing the formula in the status cell or elsewhere?

Open to suggestions and thanks as always for your help!

KPH

Do you want to highlight rows 4, 5 and 6 or columns 4, 5, and 6 on the row with the date in? The formula will need to be on the row that you want the highlighting to be on.

Charmainem3

Apologies for not being clear. I'd like for rows of 4, 5, and 6 to be highlighted. I'm thinking the auto color change of the task name row will catch their eyes better.

KPH

Thanks for confirming. I was writing a comment about how to do that and then thought you might have meant columns. To make cells in rows 4, 5, and 6 change color there must be something happening on those rows to trigger the change. This could be a formula that does the math to check the difference between the two date cells you mention, and checks a box if less than 14. Then you can set up conditional formatting to change the color if the box is checked.

In other words, add a checkbox column called "late cabling" or similar. In that column put a formula that will be something like this where the parts in bold are the cells you need to refer to.

=IF( cable due date - other date <14,1)

Then set up conditional formatting to change the color of the task name on any rows where the "late cabling" box is checked.

I hope that helps. If you have any questions, please share a screen shot of your sheet so we can provide more specific answers.