/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
mso-fareast-font-family:”Times New Roman”;
mso-bidi-font-family:”Times New Roman”;}
margin:2.0cm 2.0cm 2.0cm 2.0cm;
/* Style Definitions */
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
font-family:”Times New Roman”;
I have blogged previously about what a rubbish job MS have done with Conditional Formatting in Excel 2007, but it seems it is worse than I feared.
In this same file I now had CF setup as I wanted, after much trials and tribulations, but I needed to insert a new column, but the CF was required to be different than the CF on either side.
First thing I tried was to simply insert the column. Of course, this meant that it inherited the CF from the column to the left, which was not what I wanted, so I opened the CF dialog and thought I would change the range that the rule applies to from those two columns to just my newly inserted column, and change that rule. This I did, but it wiped out the rule entirely from the other column. And to compound the problem, it cleared the format fill colour from the newly inserted column, and when I changed the rule and added back the fill colour, every cell in my range showed the colour, even though only a single cell matched the condition being tested for.
So I tried another approach. I created a column off the data area, setup the CF exactly as I wanted for that column, then added an index number in the first free row so that I could sort the columns into my required order. For some unknown reason, this removed my CF, gave it the CF of the column to its (new) left, and also managed to setup data validation as was applied to its (new)O column to the right. Genius! I knew what I wanted to do, but the designers at MS decide they know better, in two ways.
So how did I fix it? I first deleted the CF from the column that is already present, then inserted my new column. I then re-instated the CF that was in the previous column, and added the CF in my new column. Not a great chore, but I SHOULDN’T HAVE TO FIGHT THE SOFTWARE to do what I want to do. And you can bet that the next time I need to do it, I will have forgotten, and I will go through all this pain again.
Whilst I am having a good rant, when are MS going to default CF (and Data Validation) edit boxes to update, so as to avoid accidentally inserting rubbish when you hit the arrow keys. I can’t recall ever thinking the default was useful.