EXCEL - How to copy a rule WITHIN conditional formatting's "Manage Rules"?

Question

I need to highlight cells different colors with white text based on a formula. The rule that got it to work properly cannot be selected with one of the shortcuts. The cell's highlights depend on another cell's content. I have to manually enter all the details and a formula for it to highlight properly.

The process is:

Add rule

Change style to "Classic"

Change next to "Use a formula to determine..."

Enter formula (the one thing I can copy and paste, and change the parameter). It's =ISNUMBER(SEARCH("[text]",[Next cell]))

Change format to "Custom format..."

Change font color

Click "Fill" menu

Change Background color

Click "OK" 3 times

The problem is I need to do this at least times, and it would be quicker if I could duplicate or copy the rule within the "Manage Rules" pane, and tweak from there, instead of starting each one from scratch, making sure I get the formula right each time.

Is there a way to duplicate/copy a rule within this window?

If I could then it would be reduced to:

Duplicate rule

Edit rule

Change [text] within formula

Click "Custom format"

Change background color

Click "OK" 3 times

I am not asking how copy conditional formatting to other cells (what every help topic points me to).

EDIT: I tried formatting one cell with the same formatting of another to see if it would add on, but it still has only rule.

Thanks for any help/information.

Answer 1

Apparently what you actually want to do is add an existing CF rule from one range as an additional CF to another range which already has at least one rule applied. I'm afraid that wasn't at all clear in your original message.

If that interpretation is accurate I don't know of any way to do so. It may be possible with VBA but that's out of my realm.

Answer 2

Hi jimlapbap,

Thanks for asking the question in our community forum.

As far as I know, there is currently no out-of-the-box Excel feature that can meet your needs. While you can try to find a way to achieve it using macro. If you are willing to, I can help you move this post to the macro-related category, in the hope that you can get professional assistance.

Thank you for your understanding.

Best regards,
Madoc

Answer 3

Perhaps is you approach from the opposite direction: Have you considered copying the cell(s) which have the CF rule applied, using Edit> Paste Special - Formats to apply to the next range, then editing the new instance of the rule?

Answer 4

Perhaps is you approach from the opposite direction: Have you considered copying the cell(s) which have the CF rule applied, using Edit> Paste Special - Formats to apply to the next range, then editing the new instance of the rule?

Thanks for the reply. When I wrote

"EDIT: I tried formatting one cell with the same formatting of another to see if it would add on, but it still has only rule."

that is what I meant. I tried pasting the format into an existing cell with the same format, but it still just had the one rule.

Answer 5

On the Home tab of the Ribbon, under Conditional Format choose Manage Rules

This displays a dialog where you can copy rules. From the sound of it, this dialog might be useful to you.