/* 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