Conditional Formatting, 3 conditions, relative to the previous rows cell

[rlubin]

I have created a spreadsheet for financial data, where I want to be able to change the fill colour of the cell based on relative value of the cell on its left.

Ex.

Please Login or Register  to view this content.

Based on this information I would like a couple of things to be done by the conditional formatting. I assume either an IF, OR, or XOR function will be used but correct me if I'm wrong. (which I probably am based on the fact that I can't figure this out by myself and have come here)

I want to be able to:

IF A1<B1 then B1 is green

IF A2>B2 then B2 is red

IF A3=B3 then B3 is white

I want to be able to control the colour of each cell (excluding the first cell in every row because there is no value to its left) based on its value in comparison to the cell on its immediate left (the previous cell in its row). But, I want to be able to do this in bulk because I have almost 500 cells with data that need comparison. I want it to have 3 conditions as I stated:

green if the value in the cell is higher than the cell to its left

red if the value in the cell is lower than the cell to its left

white if the value is equal to the cell on its left

Thanks for all the answers and advice.

[6StringJazzer]

You are just about there. You need three rules, one for each condition. The rules are going to look similar to what you showed, and will apply to B1:Z99 where Z99 is the lower-right cell in your range. Except I think the three rules you really want are:

=A1<B1 then B1 is green

=A1>B1 then B1 is red

=A1=B1 then B1 is white

Use the "Use a formula" option.

Another thing you can do, instead of making three rules is just make the first two. Then format all the cells to be white. Then they will be white unless one of the first two conditions is true. Same effect, fewer rules.

[rlubin]

Dear 6StringJazzer,

Thank you for you quick response and insight.

I didn't make it super clear in my thread, but I need to apply those equations to every single cell with data inside of it. So, B1>C1 is just a simplifed version. I would actually like to have the formula apply to every single cell, relative to the cell to its left. Because I plan to apply this to a large amount of data I would prefer not to write:

B3>C3, C3>D3, E3>D3...etc

B3<C3, C3<D3, E3<D3...etc

The second part of my question being:

I would like the formula to take into account the value of very cell to its left (the previous cell in such row) in the entire worksheet, not just A1, B1 and C1.

You have covered how to make the rule apply to all the data, but I also need to know how to make the formula compare a cell only to the previous cell in its row (the cell on its left), how would I go about doing that using references.

And is it possible to only use 2 rules that would cover what i require?

[sanram]

Yes. You can, if you don't have any color on any cells. Or you must have to create 3 rules for that. Click on B1, then create all 3 new rules with formula.
1st Rule :

Please Login or Register  to view this content.

. Then set the color to green.
2nd Rule :

Please Login or Register  to view this content.

. Then set the color to white.
3rd Rule :

Please Login or Register  to view this content.

. Then set the color to red.

Now go to Manage rules on Conditional formatting and replace the =$B$1 with your desired range (ex. =$B$1:$AA$10000). If you have no color in any cell then there is no need to create the 2nd rule as all the cells are white by default.

That's it.

[6StringJazzer]

The way that conditional formatting works is to define the rule in terms of the cell in the upper left corner of the range you want to format. If you write a rule that says

=A1<B1

you can apply that one rule to the entire range B1:Z99.

If you are still confused I can just install these rules into your file if you attach it.

You can use just two rules by following my earlier instructions.

[rlubin]

Thanks for both of your responses, I still can't seem to get it to work I am uploading the file now, it is incomplete but it was have all the numbers needed to test whether of not the CF works properly.

Just to make sure we are all on the same page I wrote what colours the boxes should be and why, just encase I haven't explained myself well enough.

Thanks again ladies(maybe) and gentlemen(probably).

[sanram]

Because you don't know one basic rule of creating conditional formatting. You have to write the formula for the 1st cell of your selected range. As your 1st cell is B3 and range is B3:J14 then you need to select B3:J14 first, then create those two rules. But you have to use A3 & B3 there instead of A1 & B1. Hope that make sense now. See the attachment.

[rlubin]

Thanks all for help, solved.