Condition format from previous cell

[mazzy1026]

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

 

[Andrew Poulsom]

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.

 

[Yard]

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

 

[mazzy1026]

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) ?

 

[Ketan]

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.


Check and share your experience

 

[mazzy1026]

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

 

[Ketan]

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

 

[mazzy1026]

Thanks Ketan & everyone

 

[Yard]

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.