For each cell value I would like to change the colour in an adjacent cell based on the value in another. For example: I have a column of cells with a range on numbers from 81 to 91. Where cell values are between 30 and 91 the colour is RED Where cell values are between 0 and 30 the colour is AMBER Where cell values are between 1 and 81 the colour is GREEN

1You might try search the internet for "Excel between". – BillDOe Sep 30 '18 at 19:32

the challenge is to put "to change the colour in an adjacent cell based on the value in another" relative equation in the rule.. – p._phidot_ Oct 15 '18 at 21:30
I think this is what you wanted. Basically, in Excel you can reference cells in different ways. You may sometimes realise that when you click on a cell to reference it in your formula, it's inserted as $A$2
instead of A2
. While both are correct, if for instance you start to drag around the former, the formula will be copied directly (e.g if you drag it to A3
it will not change, but remain as $A$2="Hi"
in both cells). If you do the same to the latter, the formula will adapt (e.g if you drag it to A3
it will change from A2="Hi"
to A3="Hi"
). Also note that it is possible to only use one dollar sign/locked reference, such as $A3 (where it will change when dragged on the Y axis, but not the X) or A$3 (where it will change when dragged on the X axis, but not the Y).
Keeping this in mind, we can begin to create our formula. Select the cells you want to be coloured. Click 'Conditional Formatting' > 'New Rule' > 'Use a formula to determine which cells to format'. Click on the formula box. If you just click on a cell from there to select it, it will insert it as $A$2. However, this means that each cell's colour is being determined by the cell A2, as it is locked to only look at that cell. However, if you remove the second dollar sign to get $A2, then it will still only make comparisons in the A column, while looking at the relative row, as the row is open to change. Excel will automatically line up the row with the row it is testing to format, and therefore check in the right place.
In conclusion, make three formulae, all on the same range (the one you want to be coloured). The first, =$A2>0
, and set the format to have a green fill. The second, =AND($A2>31, $A2<1)
, and have an amber fill. Finally, do =$A2<30
with a red fill.
I hope this helps.