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

  • 1
    You 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

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.