# Condition format from previous cell

#### mazzy1026

##### New Member
Morning All

In a nutshell, I need to show a cell as red or green depending on the value of the cell above it.

Say I have numbers in the range C2:C5, I want to say if the number above me is lower, then I need to be red (value gone up since previous month/quarter) whereas if it is higher, I need to be green.

I have quite a lot of rows and columns that this needs to be performed in. I've had a try with the conditional formatting, but not really getting anywhere!

Any ideas?
Cheers

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Select C2:C5. In conditional formatting, Condition 1 Cell Value Is greater than =C1, Format red, Condition 2 Cell Value Is less than =C1, Format green.

Condition 1 in C2:

Cell Value is greater than =C1 formatted to red

Condition 2 in C2

=Cell value is less than =C1 formatted to green

If you think there's a danger of rows being inserted/deleted, which would screw up these formulas, then try

Condition 1 in C2
Cell value is greater than =INDIRECT("R[-1]C",0) formatted to red

Condition 2 in C2
Cell value is less than =INDIRECT("R[-1]C",0) formatted to green

Thanks Andrew

I tried that, but it only works off cell C1

-----

So if I say greater than C1, does this not apply to all cells or does the cell reference increase (>C2, >C3 etc) ?

Last edited:
I would like to use following :

1)Select the entire range and make cell color Green
2)In conditional formating, use formula=(c2>c1) then format the cell red color.

My point is though, if you say C2>C1 - then what about when you get to C10 for example?

you need to use the brush and copy the format to your entire range.

at c10, the formula will become c10>c9.......and depending on the value in the cell color will change / appear

Thanks Ketan & everyone

And I think to be fair to Andrew, he said
Select C2:C5

If you had done that and applied the formatting as described, you would have seen it apply to all the cells in that range.

(I should have added the same instruction in my post to make it clearer.)

NB. if you are using Excel 2007, the formula used to drive the conditional formatting will always appear in the Name Manager as the upper left cell in the range to which the formatting applies. i.e. in this case, the formula would always say C1 but would in fact apply relatively to each cell in the range. End result is the same.

Replies
1
Views
153
Replies
7
Views
171
Replies
5
Views
398
Replies
14
Views
559
Replies
8
Views
359

1,207,011
Messages
6,076,144
Members
446,187
Latest member
LMill

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back