Excel 2010 Conditional Formatting Disable Cell Alignment

bisel

Board Regular
Joined
Jan 4, 2010
Messages
223
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0
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.
 
Upvote 0
You can't change font or size in the CF dialog.
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.


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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top