How to fill cells based on complicated formula?

问题:

I have a complicated formula. What I'm looking to do is to set it up where if column A is YR then column B-J will highlight red if the date is more than 2 years ago and yellow if the date is within 30 days of reaching the 2 year mark.

If column A is P1, P2, P3, P4, or P5 then column B-J will highlight red if the date is more than 1 year ago and yellow if its within 30 days of reaching the one year mark.

回答1:

You will want to create two conditional formatting rules. First select B2:J6 (as laid out in the image below, the bottom row will vary with your own data) with B2 as the Active Cell. Create a new formula using the Use a formula to determine which cells to format and supply the following for Format values where this formula is true:

=AND(OR($A2="P1",$A2="P2",$A2="P3",$A2="P4",$A2="P5",$A2="YR"),B2<=EDATE(TODAY(),(1+($A2="YR"))*-12))

Click Format and supply a red Fill. I also added a white Font for readability. Click OK to accept the format and then OK again to create the new rule.

With B2:J6 still selected, repeat with the following formula for a yellow fill.

=AND(OR($A2="P1",$A2="P2",$A2="P3",$A2="P4",$A2="P5",$A2="YR"),B2>EDATE(TODAY(),(1+($A2="YR"))*-12),B2<=(EDATE(TODAY(),(1+($A2="YR"))*-12)+30))

You results should be similar to the following.