Results 1 to 9 of 9

Excel 2010 Conditional Formatting Disable Cell Alignment

This is a discussion on Excel 2010 Conditional Formatting Disable Cell Alignment within the Excel Questions forums, part of the Question Forums category; I am perplexed and confused. Having recently moved from Excel 2003 to Excel 2010 I am having a learning curve ...

  1. #1
    New Member
    Join Date
    Jan 2010
    Location
    Arizona
    Posts
    15

    Default Excel 2010 Conditional Formatting Disable Cell Alignment

    I am perplexed and confused. Having recently moved from Excel 2003 to Excel 2010 I am having a learning curve to ride again ... but that aside, I have a problem I cannot resolve ...

    I have a group of cells with data validation rules to allow only a Y or N as the cell values. The normal cell background is light yellow and I use conditional formatting to change the background to light pink if the cell value = Y, thereby displaying yellow cells for N and pink cells for Y. In Excel 2010, when the result of conditional formatting is True (ie., cell = Y), the background turns pink but the cell alignment changes from center aligned to left aligned. There is nothing I can do stop that from occurring. Has anyone seen this situation? Is there a solution to maintain the cell alignment and stop it from becoming left aligned?

    Thanks,

    Steve

  2. #2
    wut
    wut is offline
    Banned
    Join Date
    Dec 2010
    Posts
    229

    Default Re: Excel 2010 Conditional Formatting Disable Cell Alignment

    Just set some random rule and tried it out with the cell centered. Excel didn't change the alignment.

    I'd suggest you post the rule you're using and explain exactly how you set it up.

  3. #3
    New Member
    Join Date
    Feb 2011
    Posts
    3

    Default Re: Excel 2010 Conditional Formatting Disable Cell Alignment

    bisel - did you find a fix? i am having the same issue too. The rule is Cell Value = "C", and sets the font colour to orange. It was set up in Office 2007 and was fine, its just got issues with 2010. Not only do i lose alignment (i can't get it off left, bottom) but i cannot edit the font or font size in the formatting box when i edit the rule.

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    23,328

    Default Re: Excel 2010 Conditional Formatting Disable Cell Alignment

    You can't change font or size in the CF dialog.

  5. #5
    New Member
    Join Date
    Feb 2011
    Posts
    3

    Default Re: Excel 2010 Conditional Formatting Disable Cell Alignment

    thanks, my bad. What about the alignment? the normal controls (out with the CF edit box) are making no impact

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    23,328

    Default Re: Excel 2010 Conditional Formatting Disable Cell Alignment

    The alignment should be set in the usual way. Is there anything different about these cells (e.g. part of a pivot table, or Table)?

  7. #7
    New Member
    Join Date
    Feb 2011
    Posts
    3

    Default Re: Excel 2010 Conditional Formatting Disable Cell Alignment

    Nope, normal cells. CF is only thing activated on them. When i put in text that does not match the CF (eg enter "A") they return to their Middle Center alignment. They also work fine if I open the file in Office 2007 or 2003.

  8. #8
    New Member
    Join Date
    Jan 2010
    Location
    Arizona
    Posts
    15

    Default Re: Excel 2010 Conditional Formatting Disable Cell Alignment

    I have found a way that I can consistently demonstrate this action. If you create a virgin Excel 2010 workbook, then conditional formatting works fine. You can adjust the cell alignment and CF does not affect it. In my case, if I open a workbook created in Excel 2003, then CF will change the alignment and fix it so you are unable to make any changes to it at all. So the issue seems to be a compatibility one ... i.e., Excel 2003 to Excel 2010.

    Steve B.

  9. #9
    New Member
    Join Date
    Jan 2010
    Location
    Arizona
    Posts
    15

    Default Re: Excel 2010 Conditional Formatting Disable Cell Alignment

    I have learned in another forum that when you convert an Excel workbook from say Excel 2003 to Excel 2010 (may also be true for migrating from Excel 2007 to Excel 2010), that the sheet properties may not be set to enable format conditions calculations. If you click on the Developer tab in the menu ribbon and then select properties for the sheet you are on, you will see a property called, "EnableFormatConditionsCalculation". Set this value for TRUE and do this for every sheet in the workbook. This seems to have resolved many problems I am having with Excel 2010 conditional formatting. Mainly the refresh of cells based on specific values or formulas.

    Steve

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com