Overlapping 'Conditional formatting' and Format painter in Excel 2007

Hi everyone,

I would be grateful if someone could help me with a problem related to format painting conditional formatting to other cells in Excel 2007.

The problem comes from continually having to use Excel 2007 at home to work on spreadsheets for work which are 2003 versions. The compatibility conflicts invariably come from overlapping conditional formatting, and part of the problem is having only just discovered what seems to be (maybe its something I don't understand) a bug in 2007?

In Excel 2003, if you painted a format from one cell to another, it completely replaced whatever format was there, including the conditional formatting.

In 2007, if you paint a format onto a cell, it doesn't replace the conditional formatting, but merely adds to the conditional formatting which was already there .... even if it's the exact same conditional formatting! As an example, if I paint a format from A1 to B1 that says if the cell says "Yes" colour it red, and I paste the format into that cell 8 times, in the 'Manage Conditional Formatting' there will be eight copies of the exact same conditional formatting saying if the cell says "Yes" colour it red.

To use the paint metaphor, when I paint a door, I like to take the all the paint off back to the bare wood, not continually paint on top of the old paint

Just wondered if there was any option to change the way format painter works in 2007 so it works the same as it does in 2003?

Thank you everyone/anyone for any help you can offer.

I don't think so mate, it is a perennial problem. I hate CF in Excel 2007/2010, they really messed it.

Hi Bob,

OK, thank you. Looks like I'm stuck with it then. The (almost) unlimited number of CFs is useful, but the way it seems to work just seems ludicrous.

Thank you again.

Originally Posted by SpecialBrew

Hi Bob,

OK, thank you. Looks like I'm stuck with it then. The (almost) unlimited number of CFs is useful, but the way it seems to work just seems ludicrous.

Thank you again.

I agree. I have blogged a couple of times about the problems that I have had with CF in Excel 2007/2010, so much work to sort it out.

Thanks for confirming my experience, it's a horrible waste of time to check for CF rules that I didn't ask for. I hate it.

Simply clear all formatting from the target cells before copying the new formatting to them. That way all former CF rules are deleted before you add new ones.