I think I have found a bug in Excel 2010 conditional formatting and wondering if anyone else can confirm, or even better,
provide a workaround.
My version is 14.0.6023.1000 (64 bit)
The problem relates to Conditional Formatting with Color Scales.
When copying below a range to extend it, rather than extending the conditional formatting range, it creates a new range.
If the initial range extends to the new cells below, it works unless you are copying more than one column,each with
independent color scales set for different sub ranges.
This can be reproduced as follows:
in A1 type 1in A2 type = A1+1copy this down to row 8.now copy this across to column B
You should now have 1 to 8 down column A and 1 to 8 down column B.
Now set a conditional format color scale for the range A1:A10 (includes two blank rows)Now set a conditional format color
scale for the range B3:B10 (note that we start here two records below the other one)Now select cells A8:B8 adn copy the two
I have attached my file where I have done the above steps.
You will note that the conditional formatting now shows
independent rules for the individual copied cells rather than incorporating them into the standard rule previously set up. In
fact it has removed them from the other range.
Very frustrating behaviour.
Any ideas on how to overcome
this? I have tried reporting to it Microsoft but have not found the right channel.