Thanks Rick, that trick is a good standby for expanding the range of conditional formats into new cells, I used it a lot in Excel2003. I have since become somehat averse to this technique, as it tends to punch holes in the cell ranges of existing conditional formatting used in my tables. By pasting with fomratting, you can sometimes end up with a mess of duplicate rules on balkanized ranges of cells. I almost always paste values specifically because of this little annoyance.
I appologize for neglecting to specify I am working in Excel2007 and the place I would like to be able to duplicate an existing rule is in the new manage rules dialogue.
Here is an example case: I am managing several parallel tables across multiple sheets, using conditional formatting to color rows according to the valule of a column, Status. If I have 5 diffferent conditional formats on the table and I want to add two new states of Status, I would like to be able to duplicate an existing formatting rule for a starting point, change the text that I am matching for and change the color of the formatting, and then this new rule will apply to the same cell range in the table as the original rule.
As it stands now, I make a new rule that is blank, switch to an existing rule and edit it to copy the formula, close this rule, edit the new blank rule and past the formula, change the little bit of text in the formula for the new token, set up the new formatting from scratch, then colse the rule and copy the cell range from an existing rule to the new rule. Then I switch to the next sheet and do it all over again. This all just feels a little convoluted to me, and is much more manually intensive that it needs to be.
My request is for a duplicate button in the manage conditional formats dialog of Excel2007.
-jason (I suppose I should make an account)