Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CONDITIONAL FORMATING: 3 COLOR SCALE REFERENCED TO A DIFFERENT CEL | Excel Discussion (Misc queries) | |||
autoshape color conditional formating | Excel Discussion (Misc queries) | |||
Change color without conditional formating | Excel Discussion (Misc queries) | |||
Conditional Formating Background Color | Excel Discussion (Misc queries) | |||
Conditional Formating - Different row color also when using filter | Excel Discussion (Misc queries) |