3 COLOR SCALE CONDITIONAL FORMATING WITH FORMULAS
 

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.

3 COLOR SCALE CONDITIONAL FORMATING WITH FORMULAS
 

Change the $A$1 reference to $A1
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"R. Arizpe" wrote in message
...
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.

3 COLOR SCALE CONDITIONAL FORMATING WITH FORMULAS
 

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" wrote:

Change the $A$1 reference to $A1
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"R. Arizpe" wrote in message
...
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.

3 COLOR SCALE CONDITIONAL FORMATING WITH FORMULAS
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"R. Arizpe" wrote in message
...
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" wrote:

Change the $A$1 reference to $A1
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"R. Arizpe" wrote in message
...
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.