Forum Discussion
Highlight Date Cell based on proximity to todays date AND another controlling cell
Hello Everyone,
I've read multiple posts on this topic but did not find one that exactly worked for me so I would appreciate everyone's help.
My workbook currently uses conditional formatting to color-fill rows based on the value of column H. I also want to add the following date cell highlights into the mix if possible.
- If employee is on "LOA-Active" (column H), I want date in column L to highlight YELLOW 1 week prior to their return date (column L) to show employee will be returning back from leave in the next week.
- If employee is on "LOA - Active" (column H), I want date in column L to highlight RED once the date listed in column L has passed to show employee has not returned from leave
- If employee is scheduled for a future leave "LOA - Future" (column H), I want date in column K to highlight yellow 1 week prior to date in K to show they will be going out on leave soon.
I need the controlling cell to be column H as "LOA-Active" highlights Column L whereas "LOA-Future" highlights column K. Once an employee returns from leave column H is marked as "LOA-Returned", then moved to the very bottom of the spreadsheet. I want the column L highlights to then disappear.
That sounds like a lot once I typed it all out.......Your help is so much appreciated.
- Riny_van_EekelenPlatinum Contributor
I replicated your schedule, though a bit simplified, and added the CF rules that do what you asked for. At least, I believe I understood it correctly.