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

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

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

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...