Excel 2010 Conditional Formatting Disable Cell Alignment

[bisel]

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

 

[dragonabsurdum]

OK, I know this thread is a few years old now, but I just ran into this same problem, and I found a solution that may work for others. If the file is in the old .xls format, just do a Save As and choose the new .xlsx format, or if it has macros/vba, choose .xlsm format. When I did this, the alignment errors in CF went away completely.

I am having this issue with a macro-enabled Excel 2013 workbook. It is already in the .xlsm format, and the properties for each sheet already have "EnableFormatConditionsCalculation" set to TRUE. I can't figure out why on earth my cell alignment properties are being overwritten when my conditional formats kick in. Has anyone found any other solutions for this bug?

 

[Milescf85]

1 year after last post I am adding to the thread. After attempting both the extension (xlsx, xlsm) and developer solutions to no avail, i just copied sheets into a new book and the alignment bug was fixed.

 

[Tom McLernon]

I had this same problem too, and the source file was already .xlsx. No amount of different tactics seems to fix it. I only had the one sheet with about 1,000 lines, and I copied and pasted it to a new instance of Excel which fixed this issue. (but I don't know what the cause is.) If you have a workbook with links and pivots etc. a copy-paste solution may not work for you.

 

[lacastellanos]

1 year after last post I am adding to the thread. After attempting both the extension (xlsx, xlsm) and developer solutions to no avail, i just copied sheets into a new book and the alignment bug was fixed.

This worked for me. Thanks!!