Conditional Formatting not using cell references

[leenux_tux]

Hello Forum,

I'm looking into a way of using conditional formatting to colour the text in a cell based on the value in the cell versus the value in a cell directly to it's left. I can do it using cell references, however, this means the conditional formatting needs to be recreated for each cell I want to run the test for. Is there a way to just say "compare the value here with the value to the left ?"

Here is some sample data

	A	B	C	D
1	35	40	38	40

<tbody>
</tbody>

So, I want to compare B1 with A1, if higher, format text green, if lower, format red, if the same format orange.
then compare C1 with B1
then compare D1 with C1
etc
etc

Without using the actual cell reference, can it be done ? Or, if anyone has another "smart" way of doing this i', all ears.

Thanks in advance

 

[AliGW]

If you set up a rule for C1 to compare it with B1, e.g. =B1>C1, then when you extend the CF range that it applies to, the formula will adjust itself accordingly because you have not anchored the cells with the dollar sign. You won't see this change in the CF dialog, however, which makes it a little bit tricky, but you will see your formatting properly applied.

 

[Eric W]

You can use relative references in CF formulas. For example, select B1:D1. Click Conditional Formatting > New Rule > Use a formula > and enter
=B1>A1
then click Formatting... and choose a green text color.

Always enter the formula based on the upper left cell in the range you selected. Then CF will adapt the formula to the rest of the cells in the selected range as if you entered it in B1 and just copied it to the other cells. So the formula evaluated for C1 would be =C1>B1 and the formula for D1 would be =D1>C1.

Hope this helps.


AliGW: Nice hat!

 

[AliGW]

Thanks, Eric! I like to get festive.

 

[leenux_tux]

Wow !!! Thanks for the fast response folks !!

Just to confirm, even though I have referenced the cell using using the XY coordinates (but not using the $) when I copy the formatting to other cells (or use the format painter) the CF will be applied correctly, however, when I look at the CF it will not look like it's applied to the correct cells ? Just seems a little odd to me.

Thanks in advance