Forum Discussion
Solved
Conditional Formatting multiple rules
I am trying to conditionally format a cell that is green for dates in the future (from today), red for dates in the past (before today) and black for two criteria, 1) date in the past and 2) when a c...
I think the attached version does what you want, with 5 rules, one for each color (black, green, light blue, amber and red).
Ah yes good spot, I've have amended that.
So essentially I'd like the status in D and the date in column B to talk to one another. I'd like the criteria to be something along the lines of:
If D2 is marked as 'complete' and the date is in column B2 is in the past then then cell in F2 should be black
If D2 is marked as 'complete' and the date in B2 is equal to todays date or in the future then F2 should be light blue (to flag for attention)
If D2 is marked as 'in progress' and the date in B2 is in the past then F2 should be red
If D2 is marked as 'in progress' and the date in B2 is equal to todays date or in the future then F2 should be green
If D2 is marked as 'not started' and the date in B2 is in the future then F2 should be green
If D2 is marked as 'not started' and the date in B2 is on the past then the F2 should be red
If D2 is marked as 'at risk' and the date is in the past then F2 should be red
If D2 is marked as 'at risk' and the date in the future then F2 should be amber
I'm not sure if I'm completely over complicating things and tying myself up in knots.
Thanks for your help
Claire
So essentially I'd like the status in D and the date in column B to talk to one another. I'd like the criteria to be something along the lines of:
If D2 is marked as 'complete' and the date is in column B2 is in the past then then cell in F2 should be black
If D2 is marked as 'complete' and the date in B2 is equal to todays date or in the future then F2 should be light blue (to flag for attention)
If D2 is marked as 'in progress' and the date in B2 is in the past then F2 should be red
If D2 is marked as 'in progress' and the date in B2 is equal to todays date or in the future then F2 should be green
If D2 is marked as 'not started' and the date in B2 is in the future then F2 should be green
If D2 is marked as 'not started' and the date in B2 is on the past then the F2 should be red
If D2 is marked as 'at risk' and the date is in the past then F2 should be red
If D2 is marked as 'at risk' and the date in the future then F2 should be amber
I'm not sure if I'm completely over complicating things and tying myself up in knots.
Thanks for your help
Claire
I think the attached version does what you want, with 5 rules, one for each color (black, green, light blue, amber and red).
- Genius! This is exactly what I was looking for 🙂 Thanks so much