Home / Formatting / Multi Rule Conditional Formatting

Multi Rule Conditional Formatting

The Excel HelpDesk Support team received the following request for help:

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.

screen-shot-before

 

conditional-formats-before

 

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

conditional-format-middle-fixed

 

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

screen-shot-after

 

If you would like a copy of a sample spreadsheet that includes the conditional formatting click on the following download link

Download Sample File

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.

About Excel Help Desk