atp

I needed to have a cell have a yellow or red background colour based on its contents. I also needed a second cell to go yellow or red, depending on whether the first was yellow or red.

The secret is conditional formatting and a new style.

First make a new style.

• Format -> Styles and formatting
• right click and select new style
• set name to "redbackground", set the background to red, and set the right number formatting, press ok
• Repeat for yellow.

Now for the straight forward case of making the cell background have the correct colour, select the range of cells that are important, and;

• format->Conditional Formatting
• Under Condition 1, set "cell value is" "greater than" 700
• Set Cell Style to "redbackground"
• Tick Condition 2
• For condition 2, Set "Cell Value is"  "greater than" 500
• Set Cell Style to "yellow background"

It seems that the logic stops as soon as it has a match, so you need to put the most restrictive first.

So for the next bit, we need to set the background colour of the cell depending on the value in another cell.

• Select the new data range
• Format -> Conditional Formatting
• Under Condition 1, Change "Cell Value is" to "Formula is" "B2 > 700"
• Select the Cell Style to be "redbackground"
• Under Condition 2, Change "Cell Value is" to "Formula is" "B2 > 500"
• Select the Cell Style to be "yellowbackground"

And here you are;