CONDITIONAL FORMATING: 3 COLOR SCALE REFERENCED TO A DIFFERENT CEL

[R. Arizpe]

In Excel 2007.
From the following example:

A B
1 3
2 7
3 5
4 10
5 12
6 8
7 6
8 3
9 1

Suppose that in my worksheet, the values in column A will
always be in the range of 1 to 12.

I want to establish a conditional formatting on the corresponding
cells in column B, so that the cell color will be set by a 3
color scale (like green, yellow,red) according to the value of
the corresponding cell in column A.

So, B5 would be colored in RED (A5 has the max value of the scale),
B7 would be yellow (A7has the midpoint value), and B9 would be
green (A9 has the min value).

The only way I have been able to do this is by entering 12 different
rules in the conditional formating, one for each possible value in
column A, and setting one different color (different RGB values)
for each condition.

I would like to know how to enter a formula in the 3 color scale
conditional formatting dialog box that would give the same result.

 

[Bob Phillips]

Use CF formulae of

=B1=MAX($B$1:$B$10) - 'Red
=B1=MEDIAN($B$1:$B$10) - Yellow
= B1=MIN($B$1:$B$10) - Green