The Excel HelpDesk Support team received the following request for assistance:
Problem – Multiple Conditional Formatting not working
I’m trying to format cells A5-A18 with 3 rules, I have 2 rules complete.
Here’s my problem: The third rule would be to turn the cell “RED” after 30 days from today. I have A1 as my NOW cell……I’ve tried everything, my other 2 rules must be interfering? Please help
The following screenshots the spreadsheet that was provided and the existing rules that had been created.
Step 1 – Middle Condition (YELLOW) not a range
The middle condition is not a range, this is the first issue for this set of conditional formats. For the formats to apply correctly each one must apply to it’s own set of criteria that cannot overlap. We will change this to be between “NOW()-10” and “NOW()-30”. The conditional format for the middle condition should look like
Step 2 – Final Condition (RED) not right
The final condition instead of showing only those values that were greater than 30 days prior to the current date was not matching to any condition as the condition was attempting to match “NOW()>30” which will never equate to TRUE. A simple change in this conditional format to be “<NOW()-30” was made and the condition then correctly evaluated those values greater than 30 days prior to the current date.
Result – Conditional Formats applied
The screenshot shows the correct conditional formats applied to the table
If you would like a copy of a sample spreadsheet that includes the conditional formatting click on the following download link
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.