Results 1 to 5 of 5

Thread: Baffling problem with conditional formatting and indentions

  1. #1

    Angry Baffling problem with conditional formatting and indentions



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

    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.

    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!

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,271
    Articles
    57
    Blog Entries
    14
    Excel Version
    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...
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Quote Originally Posted by Ken Puls View Post
    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.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,271
    Articles
    57
    Blog Entries
    14
    Excel Version
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Quote Originally Posted by Ken Puls View Post
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •