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.