# Conditional formatting / Formula

#### Rebecca Kowalski

##### New Member
Hey all,

I have a spreadsheet of number data with dates in ascending order in the first left column.

I want to apply a format so that for example:

If the number in Cell B3 is greater than the number in B2 (turn the text red)
If the number in Cell B3 is less than the number in B2 (turn the text green)

But I need this to be continuous, so it's the same for cell B4 against cell B3, cell B5 against cell B4 (and so forth)

Is this possible? I have tried using conditional formatting but can't get it to work on one let alone my entire spreadsheet.

Thanks
Rebecca

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Here's an example of the desired outcome

 Date Data 01/09/2016 35,064 02/09/2016 34,836 03/09/2016 34,836 04/09/2016 34,836 05/09/2016 34,631 06/09/2016 34,645 07/09/2016 34,642 08/09/2016 34,439 09/09/2016 34,452 10/09/2016 34,452 11/09/2016 34,452 12/09/2016 33,774 13/09/2016 33,606 14/09/2016 33,525 15/09/2016 33,333 16/09/2016 33,249 17/09/2016 33,249

<colgroup><col><col></colgroup><tbody>
</tbody>

Rebecca Kowalski, Good morning.

Supposing your VALUE data is at B2:B100

Try to use:
Select B3:B100
Conditional Format Menu --> Using Formula

Rule 1
=B3 < B2
<b2
Format: Font Color GREEN
OK

Rule 2
=B3 > B2
Format: Font Color RED
OK

Is that what you want?
I hope it helps.</b2>

Select range B3:B18
apply following CF =\$B3>\$B2 and format red
=\$B3<\$B2 and format green

It may be due to I don't want to formula to always be worked against B3 I want it to be against the cell prior to the selected cell. So B4 against data is B3, then B5 against data in B4, etc...

Rebecca Kowalski,

The formulas suggested in both messages act like YOU WANT.

"...It may be due to I do not want to formulate to always be worked against B3 I want it to be against the cell prior to the selected cell..."

But if you want this to happen when you select, that is to click on any cell, the others work this way, this is only possible with VBA Excel Programming.

Please, explain it a little better.
Remember to show us how you expect to see the result.

It may be due to I don't want to formula to always be worked against B3 I want it to be against the cell prior to the selected cell. So B4 against data is B3, then B5 against data in B4, etc...

to enter the formula for each cell, but for the entire range.
The fact that the formula is absolute for the column and relative for the row ensures that it does what you want.
It works perfectly for me as per your example
You do not have

Replies
1
Views
127
Replies
11
Views
268
Replies
3
Views
106
Replies
1
Views
250
Replies
2
Views
124

1,196,409
Messages
6,015,106
Members
441,870
Latest member
kojack

### 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