PDA

View Full Version : Conditional row formatting based on multiple nested IF/AND statements



FabGab
03-22-2015, 03:02 PM
I am trying to highlight cells in a column based on a rage of dates while factoring in the values in another column. For example:

If A2 is “Tier 1” then apply the following conditional formatting rules:
If A3 is more than 90 days from today, then shade whole row RED
If A3 is between 46-90 days from today, then shade whole row YELLOW
If A3 is 45 or fewer days from today, then shade whole row GREEN

If A2 is “Tier 2” then apply the following conditional formatting rules:
If A3 is more than 180 days from today, then shade whole row RED
If A3 is between 91-180 days from today, then shade whole row YELLOW
If A3 is 90 or fewer days from today, then shade whole row GREEN

If A2 is “Tier 1” then apply the following conditional formatting rules:
If A3 is more than 270 days from today, then shade whole row RED
If A3 is between 181-270 days from today, then shade whole row YELLOW
If A3 is 180 or fewer days from today, then shade whole row GREEN

If A2 is “Tier 1” then apply the following conditional formatting rules:
If A3 is more than 540 days from today, then shade whole row RED
If A3 is between 271-540 days from today, then shade whole row YELLOW
If A3 is 270 or fewer days from today, then shade whole row GREEN

I’m have been struggling with how to do this for the past few days. Any help would be appreciated.

Bob Phillips
03-23-2015, 01:49 AM
Red: =$A$3>IF($A$2="Tier 1",90,IF($A$2="Tier 2">180,IF($A$2="Tier 3",270,540)))
Yellow: =$A$3>IF($A$2="Tier 1",45,IF($A$2="Tier 2">90,IF($A$2="Tier 3",180,270)))
Green: =$A$3>0