Conditional formatting problem

[bibbyd01]

Hi All

I'm trying to set up conditional formatting in excel 2007 and have no problem setting it up for 1 cell, but when I want to copy it down the column, it bases the formatting on only 1 cell, and I can't change it.

So, for instance, in column A I have expected delivery, column B I have actual delivery, and if B2 is greater than A2 I want it to highlight red. That works fine. When I copy the formula down to cell B3, the rule changes to $B$2:$B$3. I can edit each cell individually, to create it's own rule, but this could take decades. I can't ever remove the absolute values.

Is there an option somewhere? Or am I doing something completely wrong?

 

[VoG]

Hi.

It should work if you use

=B2>A2

 

[bibbyd01]

Hi

I've set up the rule correctly. What I didn't make clear was that the range changes to $B$2:$B$3 when I copy down to the next cell, but the cell it checks against is still A2

 

[VoG]

Try this: select B2 to the last used cell in column B, use the CF formula

=B2>A2

and apply a format.

I'm pretty sure that in Excel 2007 if you then look at the CF formula in say B7 it will be shown incorrectly as

=B2>A2

but it is correctly applied (i.e. using =B7>A7).

 

[bibbyd01]

I'm afraid it doesn't work

I've come up with a workaround of creating a new column and getting it to format the cell based on a number. Then, in the cell, I have an IF statement, providing a number depending on whether it's early or late.

 

[VoG]

Working here

Excel Workbook
	A	B
1	A	B
2	3	7
3	10	10
4	5	1
5	1	7
6	4	3
7	3	2
8	10	4
9	1	4
10	5	1
11	9	4
12	9	5
13	5	3
14	8	3
15	4	3
16	8	3
17	10	2
18	3	8
19	10	7
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =B2>A2	Abc