Excel 2007 : Conditional Formatting Bug?

[Pauleyb]

I consider myself a fairly strong Excel user, so I am at a loss on what is happening here. Basically, my conditional formatting works if the test expression is an absolute reference but fails if it is a relative reference. Here is my example.

1) Open up a new excel spreadsheet
2) Add these numbers in a row: 1 2 3 4 3 2 1 2 3 4 (that should be 10 cells with numbers in them)
3) Conditional format:
a) Cell Value equal to =min(your_cell_range)
b) Applies To =your_cell_range
c) Don't forget to make some format change (like making the text red)
4) As default, Excel will use absolute values for your_cell_range and it should work as expected by only highlighting the cells with a 1 in it.
5) Now, select your_cell_range, Manage Rules, and Edit the rule you just added. Change the absolute range in your min() equation to a relative range (that is, remove the $ signs in the range).
6) Apply this change and now the 1's are still highlighted, but so are the last 2, 3, & 4.

Changing the Applies To from relative to absolute does not seem to fix things, nor does toggling the 'Stop if True' checkbox.

This is a pain, since I want to use format painter to apply this formatting rule to other rows, but the absolute reference causes the painter to keep referencing the wrong row. With relative addressing, format painter works as expected, but I get this bug.

Can someone confirm they see this, too?
Any ideas?

Want to see something weirder? Go out about 6 cells beyond your last 4 (but still in the same row. Type in a 2. The formatting changes on your_cell_range, even though the cell that just had a 2 typed into it is not in the conditional formatting range.

Thanks,
Pauley

[DonkeyOte]

It is expected, indeed, desired functionality.

When you use Relative referencing the reference is relative to the cell against which it (the rule) is being applied (not simply the first cell in the "applies to" range)

To elaborate...

When you set to A1:J1 rather than $A$1:$J$1 it follows that for A1 the reference in use is A1:J1 however for B1 it is B1:K1, C1 it is C1:L1 and so on and so forth...

Hence G1:J1 will be red font given they are the MIN of their respective relative ranges (1x10 commencing from G1, H1, I1 & J1 respectively)

You can confirm this by changing H1 from 2 to 0 - you would find for ex. that G1 is no longer red (0 now being the lowest value in the range).

When you use $A$1:$J$1 it follows that for each range in the "applies to" section the range used is fixed as $A$1:$J$1

[daddylonglegs]

Of course if you want the formatting to work correctly and be copiable you need the column references to be absolute but the rows to be relative, e.g. make cell value equal to

=MIN($A1:$J1)

[Pauleyb]

Thanks guys for setting me straight. I guess I figured that if I highlighted one cell in the range and viewed its formatting rules, I would see the 'adjusted' cell range applicable to that cell, but that does not happen.

Now it makes sense.

Pauley