3 COLOR SCALE CONDITIONAL FORMATING WITH FORMULAS

[R. Arizpe]

I have a worksheet that looks something like this:
A B C
1 $100 $50 $0
2 $150 $150 $75
3 $300 $200 $100
4 $200 $100 $0
5 $800 $700 $200

The values of columns B and C are independent from each other,
but neither one can be larger than the value in column A (for the same row).

What I want to do is to use a 3 color scale, to format the cells
in column B (and also column C) according to what percentage the
value of the cell represents compared to the corresponding cell
in column A.

So; in a scale that goes from GREEN AT 0%,to YELLOW AT 50%
and RED at 100%
B1 should be yellow (50%), C1 should be green (0%)
B2 should be red (100%), C2 should be yellow (50%)
B5 should be some shade of orange-red (87.5%) and B5 something between
green and yellow (25%); and so on.

I made the conditional formatting work for just one cell
by making the MIN a NUMBER TYPE with value 0, then
the MIDDLE POINT a FORMULA TYPE, with the formula being =$A$1/2
and the MAX was set as a FORMULA TYPE, the formula being =$A$1.

But then if I just copy the format to the rest of the cells in the
B column; the formulas are still referenced to cell $A$1, so
the formatting formula does not work for other cells.

THANK YOU.

 

[Bernard Liengme]

Change the $A$1 reference to $A1
best wishes

 

[R. Arizpe]

Excel won't let me do it, an error message appears saying that formulas in
condictional formatting cannot have realtive references (or something like
that).

 

[Bernard Liengme]

I entered your numbers
I selected B1:B5
In conditional formatting I used; Formula is =B1<=A1*10% and I used a yellow
fill; clicked OK
Cell B1 and B4 now have a yellow fill
Excel did not complain about relative address
Do it adding and tell use EXACTLY what the error message is
best wishes