Hi all.

My goal is to apply conditional formatting on a specific cell based on the values in that cell and another cell.

Example: apply formatting to cell $E$2.

If value of D2 is <120 and value of cell E2 ,<80 then apply STYLE(“BP-Green”).

If value of D2 is between 120 – 129 and value of cell E2 <80 then apply STYLE(“BP-Yellow”).

If value of D2 is between 130 – 139 or value of cell E2 is between 80 – 89 the apply STYLE(“BP-Orange”).

If value of D2 between 140 - 179 or if value of cell E2 is =>90 apply STYLE(“BP-BurntOrange”).

If value of D2 is =>180 and/or value of cell E2 is =>120 apply STYLE(“Crisis”).

The following image may help. I want the cell background to correspond to the Image.

I am able to use the built in conditional formatting dialog for the first condition,**see screen-shot below**, but I don’t really know how to construct formula for the additional conditions.

I am assuming if this can be done it will be accomplished by using nested “IF” and “ElseIF” statement.

I have tried to begin the formula with “IF(AND but not getting desired result.

As I understand it Boolean expressions combined with AND only return the value **True** if both expressions evaluate to **True**:

**True** AND **True** returns **True**; for all other combinations the result is **False**.

My assumption is that the formula below would return **False** if cell E11 value was greater 80, but it seems that if if cell D11 is between 120 - 129 the formula returns **True** because the BP-Yellow style is applied.

=IF(AND($E$11<=80,OR($D$11=120,$D$11=121,$D$11=122,$D$11=123,$D$11=124,$D$11=125,$D$11=126,$D$11=127,$D$11=128,$D$11=129),STYLE(“BP-Yellow”),STYLE(“Default”)))”