Conditional Format, Color Scale adjacent cells

[Michael_R]

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

 

[Minty Fresh]

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]

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.

 

[Eyal W]

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