Conditional Formatting with Conditions

[Setofskills]

I'd like to do a three color conditional format on one column IF another column has a certain value in that row.

For example, if the country is USA and the sales conversion target is 20%, I want to have a rule that makes a three-color spectrum based on the actual conversion rate compared to the target, like a normal three color conditional formatting rule can.

So for the table below, the actual percentage cell for store #2 would be red, the actual percentage cell for store #4 would be light green, and the actual percentage cell for store #5 would be a darker green. The other stores outside of the USA would remain unformatted.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store #[/TD]
[TD]Country[/TD]
[TD]Target %[/TD]
[TD]Actual %[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]UK[/TD]
[TD]25%[/TD]
[TD]35%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]USA[/TD]
[TD]20%[/TD]
[TD]15%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Germany[/TD]
[TD]30%[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]USA[/TD]
[TD]20%[/TD]
[TD]21%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]USA[/TD]
[TD]20%[/TD]
[TD]35%[/TD]
[/TR]
</tbody>[/TABLE]


I know how to have a three-color conditional formatting spectrum OR have a conditional on whether or not to format a cell, but I can't do both. That is, I can't have a cell have a cell be part of a variable color format AND also have a condition.

Any help would be greatly appreciated!

 

[powersp68232]

If you put the following into another cell you can base your conditional formatting off of it.

=IF(AND(B5="USA",D5>=0.3),1,IF(AND(B5="USA",D5<0.3,D5>=0.2),2,IF(AND(B5="USA",D5<0.2),3,0)))