I have a spreadsheet that was originally created in office 2003 including conditional formats. it has now been converted to excel 2010 and if the conditional format is true the font changes - but the font choices are greyed out if I try to edit this (I don't want the font style to change).
Any ideas? Also, can anyone summarise how you use the stop if true tick box on multiple conditional formats, I seem to have problems with this if an answer changes wherein the conditional format doesn't update?
In conditional formatting, the font characteristics that you can specify are style (bold, italic), underlining, strikethrough and colour. Font name and size cannot be set for conditional formatting. This was also true in Excel 2003.
In Excel 2003 and before, only a single conditional formatting rule could apply to a cell. Processing of the rules always stopped at the first condition that evaluated to True.
In Excel 2007 and later, multiple rules can be applied to the same cell. If the first rule specifies that the background colour is yellow and the second one that the font colour is red, the cell will display red text on a yellow background, unless the first rule specifies "stop if true" to emulate the pre-2007 behaviour (then the cell would have normal text on a yellow background).
Yes, I see what's happening. Apparently this is a bug in the conversion from the Access 97-2003 format to Excel 2007-2010 format, and it always involves Cambria.
Unfortunately, there is no easy way to correct this - the font name in conditional formatting can't be set either in the interface or in VBA. So the only workaround is to delete the conditional formatting rules that exhibit this behaviour, then re-create them - the bug doesn't occur when you create a new rule in Excel 2007/2010. Sorry!
I don't have a problem redoing it - It was more a principle thing! Thanks for having a look.
I am also having a problem with the conditional formats not refreshing in converted spreadsheets unless I page down and page up or close and open the file. Any further ideas?
Conditional formatting not updating automatically in some circumstances is a known problem; it has been passed on to the Excel development team, so hopefully it'll be corrected in Excel 2010 SP2.
