Conditional formatting based on adjacent cells value

Question

I am trying to keep track of budget, what I've got if a spreadsheet with months of the year along with a name and how much spare cash is left at the end of the month like so;

| Person | Jan | Feb | March |
|--------|-----|-----|-------|
| Joe    | £1  |£10  |£8     |
| Tess   | £5  |£1   |£8     |
| Tim    | £3  |£8   |£6     |
| Jane   | £8  |£1   |£7     |

What I want to do;

I want to be able to have a formula for the conditional formatting which will colour the cell to the right red / green on if the value is greater than or less than the cell to the left. In the example I gave above the first February cell is greater than the first January cell so that would be green, the first March cell is lower than the first February cell so that would be red. I have used the below to achieve this but you need to do that for every single cell and that’s very time consuming and I was hoping I could write one formula that would do all cells;

On Joes Feb I would have the conditional code

=B2<A2

And

=B2>A2

Ideally I would just have two sets of codes, one for negative and one for positive that would do all cells.

Thanks for the help.

You are going the wrong way. The formulas: B2>C2 and B2<C2 Then apply to the entire range starting in C2 to the last cell in the last column. — Scott Craner
@ScottCraner yeah my mistake typed that up minutes from leaving work, will edit the answer now, im not 100% sure how i apply it to the full range EDIT: i got it! if you type this up as an answer i will mark it as correct sinceit pointed me in the right direction, thank you. — zoro724
I won't steal Scott's answer, I'll let him post, but to answer your question on how : Highlight the entire range of data below Feb/Mar, making sure the top left (C2?) is selected (it'll appear different). then go into conditional formatting. — Ditto
@Ditto I do not have time to properly write up an answer, feel free to do so. I may even up vote it. — Scott Craner

Ditto Ditto · Accepted Answer · 2016-04-01T19:23:55

Assuming your data is as displayed, with header "Person" in cell A1, Jan in cell B1, and "Joe" in A2.

Select C2.

Highlight range C2:D5

Click Conditional Formatting => Manage Rules

Click New Rule, select "Use a formula to determine which cells to format"

Use this formula: =c2>b2 and set your format to Green.

Click OK.

Click New Rule, select "Use a formula to determine which cells to format"

Use this formula: =c2<b2 and set your format to Red.

Click OK. Click Ok. Click OK.

That should do it ... by selecting the range first, your conditional formatting applies to the entire range. By using formulas that reference cells without "$", the logic will be applied to each cell using the formula as a relative reference (ie relative to each cell in the range).

If this doesn't quite do what you want/need, please clarify what's not working.

Conditional formatting based on adjacent cells value

1 Answers