Possible Excel 2010 conditional formatting bug

[dlmcleo1]

I just did a search on "Excel 2010 bug" and the answer to the O.P.'s question was to post here first to see if it's just a wayward setting or some such. The bug I came across was as follows:

I selected a 3 column wide (about 150 rows) area for conditional formatting based on a formula. I was extremely careful typing it in since the length of the formula means scrolling back is so difficult (can't use arrow keys). What I typed was:

=OR($D5="ptlt", $D5="fv", $D5="rtd", $D5="radar", $D5="twtt", $D5="binderxv", $D5="nolimits")

When I saw that the formatting didn't take I checked the rule and found:

=OR($D1048449="ptlt", $D1048449="fv", $D1048449="rtd", $D1048449="radar", $D1048449="twtt", $D1048449="binderxv", $D1048449="nolimits")

So how does a "5" change to "1048449"? Is that unicode or something? The strange thing is, this has happened to me twice and NOT happened several other times on the same sheet.

 

[p45cal]

what was the address of the range selected, and which was the active cell at the time of inputting the formula?

Type the formula as you would expect it to behave on the active cell.

 

[Norie]

Was row 5 the first row of the range you selected?

 

[dlmcleo1]

The range for the format was =$AG$5:$AG$137. The formula was exactly as typed in my first post (was on clipboard, now gone). It was typed exactly as though I were entering it in the cell.

Just a strange bug.

 

[p45cal]

The range for the format was =$AG$5:$AG$137. The formula was exactly as typed in my first post (was on clipboard, now gone). It was typed exactly as though I were entering it in the cell.

Just a strange bug.

But which was the active cell? That changes depending on how you selected the range in the first place (from top left to bottom right and the top row is the active cell, but do it from bottom to top and the bottom row contains the active cell).

ps, Pressing F2 toggles the edit mode while entering formulae into conditional formatting, allowing you to choose whether the cursor inserts range addresses or moves within the formula.

 

[barry houdini]

As implied in previous posts what you may have done is had the range =$AG$5:$AG$137 selected but with AG137 as the active cell.

Excel is therefore applying your original formula to that cell...and adjusting accordingly for other rows. As Excel 2010 has 2^20 = 1048576 rows then as it adjusts upwards the row referenced will fall by 1 each row so AG136 is based on row 4, AG135 is based on row 3......AG133 is based on row 1.....and AG132 is based on the row above row 1...which doesn't exist so it reverts to the last row, row 1048576.

Taken right up to row 5 that means that row is referencing 1048449.

I think that some aspects of conditional formatting in Excel 2007 and Excel 2010 are difficult to understand, especially compared with Excel 2003....but there does appear to be some logic here. You have applied a formula to a row other than the first row of your range (I assume), but excel displays the formula that applies to the first row only (in Excel 2003 each cell would show just the range that applied to that cell).

Try applying the conditional formatting again but make sure you select the range from the top so that AG5 is active cell.