Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format, Color Scale adjacent cells
I have a list of companies from which I receive periodic reports.
Each company has a weight of importance In my list I record the incoming reports saying either "Missing" or "OK" I want to highlight all "Missing" with a colour scale that reflects the company's weight. For that I would create a hidden helper column (let's call it Code) with a formula like Code = Weight * if(Received = "Missing", 1, 0) I tried Conditional formatting / Color Scale but found that it applies only to the values of the cells containing the different values (codes), but can unfortunately not be extended to adjacent cells. (or I don't know how to do it ;-) Question: is there a way to apply the color scale method to the Received column? (I know I could create individual rules for each code value, but that's not very elegant!?) Example Company Weight Received Code (expected fill in column "Received") CompA 5 Missing 5 dark red CompB 2 OK 0 no fill (white) CompC 2 Missing 2 dark Yellow CompD 3 Missing 3 light red DompE 5 OK 0 no fill (white) CompF 1 Missing 1 light yellow |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format, Color Scale adjacent cells
Hi Michael
Actually you can use conditional formatting to color a cell based on a value in another cell. The real problem here is that you can only have 3 conditions (in excel 2003, at least). With your color scale you will need 4. "Michael_R" wrote: I have a list of companies from which I receive periodic reports. Each company has a weight of importance In my list I record the incoming reports saying either "Missing" or "OK" I want to highlight all "Missing" with a colour scale that reflects the company's weight. For that I would create a hidden helper column (let's call it Code) with a formula like Code = Weight * if(Received = "Missing", 1, 0) I tried Conditional formatting / Color Scale but found that it applies only to the values of the cells containing the different values (codes), but can unfortunately not be extended to adjacent cells. (or I don't know how to do it ;-) Question: is there a way to apply the color scale method to the Received column? (I know I could create individual rules for each code value, but that's not very elegant!?) Example Company Weight Received Code (expected fill in column "Received") CompA 5 Missing 5 dark red CompB 2 OK 0 no fill (white) CompC 2 Missing 2 dark Yellow CompD 3 Missing 3 light red DompE 5 OK 0 no fill (white) CompF 1 Missing 1 light yellow |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format, Color Scale adjacent cells
Thanks for your quick response!.
I forgot to say - I work with 2007. And therefore I wanted to use the ColorScale method where I don't have to bother with defining individual rules for each value but just only one rule that fits all. "Minty Fresh" wrote: Hi Michael Actually you can use conditional formatting to color a cell based on a value in another cell. The real problem here is that you can only have 3 conditions (in excel 2003, at least). With your color scale you will need 4. "Michael_R" wrote: I have a list of companies from which I receive periodic reports. Each company has a weight of importance In my list I record the incoming reports saying either "Missing" or "OK" I want to highlight all "Missing" with a colour scale that reflects the company's weight. For that I would create a hidden helper column (let's call it Code) with a formula like Code = Weight * if(Received = "Missing", 1, 0) I tried Conditional formatting / Color Scale but found that it applies only to the values of the cells containing the different values (codes), but can unfortunately not be extended to adjacent cells. (or I don't know how to do it ;-) Question: is there a way to apply the color scale method to the Received column? (I know I could create individual rules for each code value, but that's not very elegant!?) Example Company Weight Received Code (expected fill in column "Received") CompA 5 Missing 5 dark red CompB 2 OK 0 no fill (white) CompC 2 Missing 2 dark Yellow CompD 3 Missing 3 light red DompE 5 OK 0 no fill (white) CompF 1 Missing 1 light yellow |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format, Color Scale adjacent cells
Michael, Did you ever get a good answer to this? I'm basically trying to do the same and haven't come up with a way to solve the issue... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 Conditional Format Color Cells Issue in SharePoint 3.0 | Excel Discussion (Misc queries) | |||
3 COLOR SCALE CONDITIONAL FORMATING WITH FORMULAS | Excel Discussion (Misc queries) | |||
CONDITIONAL FORMATING: 3 COLOR SCALE REFERENCED TO A DIFFERENT CEL | Excel Discussion (Misc queries) | |||
Recognition of color pattern in cells to use in Conditional format | Excel Discussion (Misc queries) | |||
color coded cells and conditional format in excel 2003 | Setting up and Configuration of Excel |