Cell fill based on value of cell in row before

[lee2121]

Hello Everyone.

I have an excel file where i need the current cell to be filled with let's say a light red colour if the value entered into this cell is the same or less than the value in the cell above it.

For example

Cell A2 = 100 and Cell A1 = 110 then cell A2 should fill red and repeat for all other cells in the row.

I think a VBA solution may be best for this. I can do this in conditional formatting but i can't set up conditional formatting rules for each cell

The cells i need this to work on are Rows 2, 4, 6, 8 and 10

 

[saboh12617]

Hello,

With conditional formatting you can just select let's say the second cell of a column, go to Conditional Formatting > New Rule > Use a Formula > And write:

= B1>B2

(or whatever cells such as B2 is the current one, and B1 the one above)

TAKE CARE TO REMOVE THE DOLLARS (and setup a highlight style).

Then just Format paint / paste to the whole row. Cells where the one above them is bigger will be highlighted according to the style you defined.

You need to remove the dollars to make the conditional formatting not absolute.

 

[Fluff]

How about

Fluff.xlsm
	B
1
2	312.775
3	304.702
4	292.655
5	270.97
6	258.811
7	259.311
8	251.107
9	245.12
10	240.007
11	237.017
12	236.736
13	232.957
14
15
16
Sheet6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B16	Expression	=AND(MOD(ROW(B2),2)=1,B2<=B1,B2<>"")	text	NO

 

[lee2121]

Hello,

With conditional formatting you can just select let's say the second cell of a column, go to Conditional Formatting > New Rule > Use a Formula > And write:

= B1>B2

(or whatever cells such as B2 is the current one, and B1 the one above)

TAKE CARE TO REMOVE THE DOLLARS (and setup a highlight style).

Then just Format paint / paste to the whole row. Cells where the one above them is bigger will be highlighted according to the style you defined.

You need to remove the dollars to make the conditional formatting not absolute.

Thank you this works

 

[lee2121]

How about

Fluff.xlsm
	B
1
2	312.775
3	304.702
4	292.655
5	270.97
6	258.811
7	259.311
8	251.107
9	245.12
10	240.007
11	237.017
12	236.736
13	232.957
14
15
16
Sheet6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B16	Expression	=AND(MOD(ROW(B2),2)=1,B2<=B1,B2<>"")	text	NO

sorry but i do not know where i should enter your code?

 

[Fluff]

Select the range you want to format, then in CF select use a formula & paste in the formula I showed.

 

[lee2121]

Hello,

With conditional formatting you can just select let's say the second cell of a column, go to Conditional Formatting > New Rule > Use a Formula > And write:

= B1>B2

(or whatever cells such as B2 is the current one, and B1 the one above)

TAKE CARE TO REMOVE THE DOLLARS (and setup a highlight style).

Then just Format paint / paste to the whole row. Cells where the one above them is bigger will be highlighted according to the style you defined.

You need to remove the dollars to make the conditional formatting not absolute.

when i use the format painter it then highlights all cells and changes the formula to below

 

[saboh12617]

Yes it is normal. It can be a bit confusing because you see a unique formula, but it applies it for each cell taking the local coordinates since we took away the $.

So for instance in B30 it checks =B29>B30 and not =B2>B1.

 

[lee2121]

Yes it is normal. It can be a bit confusing because you see a unique formula, but it applies it for each cell taking the local coordinates since we took away the $.

So for instance in B30 it checks =B29>B30 and not =B2>B1.

sorry but it only seems to be calculating the second cell in the row. If i change the value in the second cell so that it is not filled all other cells will also not be filled

 

[saboh12617]

Well maybe you should try Fluff's proposition then.
You probably misunderstood some of the instructions I gave you. I checked it again and no problem. Make sure you are referring to the correct cells in the formula.