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