Baffling problem with conditional formatting and indentions

nos402

New member
Joined
Jan 23, 2013
Messages
3
Reaction score
0
Points
0
Hoping someone can solve this mystery for me. I have 2 spreadsheets. They both have identical conditional formatting that changes the color of column A and B based on what is entered in B. I am using Excel 2010.

View attachment test.xlsx
On this one, if you type "pass" in column B, A and B turn green but A loses the indention.
If you select "pass" from the drop down menu in B instead, it retains it's indention (and all other cells that have been conditionally formatted will also regain their indention).
As soon as you press enter or delete anywhere in the sheet, all Conditionally formatted cells lose their indention again.

View attachment test2.xlsx
This spreadsheet works perfectly. Conditional formatting works and indentions remain. I can not find any differences between the two that are causing this.

The only thing I can think of is that the first one started as an Excel sheet, was uploaded to Google docs, and then later downloaded as an Excel sheet again. This is so frustrating!
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,522
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Wow... that IS bizarre...

Curious, have you tried deleting all conditional formatting from the worksheet and re-creating the rules? Does the problem persist?

Really weird that it only triggers when typed, especially since there's not option to actually create indents in conditional formatting rules...
 

nos402

New member
Joined
Jan 23, 2013
Messages
3
Reaction score
0
Points
0
Wow... that IS bizarre...

Curious, have you tried deleting all conditional formatting from the worksheet and re-creating the rules? Does the problem persist?

Really weird that it only triggers when typed, especially since there's not option to actually create indents in conditional formatting rules...

Yeah, I've completely deleted and redone the rules from scratch. So bizarre. I've compared CF settings, cell format setting, every setting I could think of. Totally stumped.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,522
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
What about running it through the compatibility checker? That would convert the CF rules to be 2003 compliant, and may reset this issue. Go to File-->Info-->Check for Issues-->Check Compatibility.

Kind of a shot in the dark, but since nothing else has worked it might be worth a try. :)
 

nos402

New member
Joined
Jan 23, 2013
Messages
3
Reaction score
0
Points
0
What about running it through the compatibility checker? That would convert the CF rules to be 2003 compliant, and may reset this issue. Go to File-->Info-->Check for Issues-->Check Compatibility.

Kind of a shot in the dark, but since nothing else has worked it might be worth a try. :)

I ended up just copying and pasting everything into a new document and all was fine. Kind of a pain, but didn't take nearly as long as I'd already spent trying to figure this out. Thanks!
 
Top