Condition Formatting

[kreases]

I have been tasked with formatting multiple cells based on two values, for example

Cell A1 is a Tier number (1, 2 or 3)

Cell B1 is a Percentage and the cell that needs formatting as follows

IF A1 is 1 and B1 is equal to 100% then cell background should be green
IF A1 is 1 and B1 is less than 100% then cell background should be red

IF A1 is 2 and B1 is greater than or equal to 99.75% then cell background should be green
IF A1 is 2 and B1 is less than 99.75% then cell background should be red

IF A1 is 3 and B1 is greater than or equal to 99.50% then cell background should be green
IF A1 is 3 and B1 is less than 99.50% then cell background should be red.

This kind of test would need to be done on every row of data.

Any ideas?

Thanks,

John

 

[njimack]

Formula for green condition:

=OR(AND(A1=1,B1=1),AND(A1=2,B1>=0.9975),AND(A1=3,B1>=0.995))

Formula for red condition:

=OR(AND(A1=1,B1<1),AND(A1=2,B1<=0.9975),AND(A1=3,B1<=0.995))

 

[kreases]

Neil,

Thanks for that.

John.