Conditional Formatting quirk (loses indention)

So I have conditional formatting using this formula: =SUM($B7="pass"). Basically if column B contains "pass" then that cell and the corresponding cell in column A turn Green. I also have column B setup as a drop down menu with "Pass, Fail, n/a."

So here's the quirk I can't figure out. If you select "pass" from the drop down menu for B1, then A1 and B1 both turn green as expected.

If I manually type "pass" in B1, then click onto any other cell, then again both A1 and B1 turn green.

Any time I press ENTER, Delete, all cells formatted with the conditional formatting lose their indention.

Also if I type in any cell and press an arrow key all conditionally formatted cells lose their indention.

If I then select "pass," "Fail," or "n/a" from the drop down in column B, all conditionally formatted cells regain their indent. I'm so baffled.

Hi nos402,

How very strange. Firstly, your formula does not need the "sum" part of it, since you are only referencing one cell. It can be shortened to =$B7="pass"

This might help? Otherwise I'm just as baffled as you. Could you possibly upload a sample spreadsheet so I can mess around with it myself?

And it gets weirder. Here is another spreadsheet, that keeps its indent. I have yet to find the difference.

I looked at your upload in #4, to begin with, there is no drop-down in any cell in B.

when you delete "pass", the CF is doing exactly what you told it to...nothing. The CF is still there, nothing has changed - it is just waiting for you to enter something you have have told it to check for.

delete "pass" then type it back in - turns green
type in fail - turns pink

if you are talking about the indenting in column A, i see no change in either file. and in any case, CF cannot be used to apply (or remove) conditional formatting (at least, not in 2007)

try to upload the file using the "go advanced" "manage attachments" so that we can see the file name that you upload (editing shows test.xlsx and test2.xlsx), maybe you uploaded the wrong file the 1st time?