Exporting conditional formatting rules

[Guest]

From within Excel 2007, can I export a set of conditional formatting rules
and then import them into another worksheet?

On Sheet "A" I've got about 12 conditional formatting rules. The rules
color certain cells based on whether a certain string of text is in the cell,
ex: HToH. I'd like for the same results in color change in the cell to
appear on Sheet "B".

I don't want to have to create the rules all over again for each sheet. The
thought of needing to edit the rules in future would be terrifying.

Thanks,
Chadwill

 

[Roger Govier]

Hi

Click on the small cell with a triangle, above row numbers. This will
select the whole sheet.
Copy>Move to Sheet2>Paste Special>Formats

 

[David Biddulph]

Which version of Excel is that, Roger?
On Excel 2003 the only thing that I could see which might be described as a
"small cell with a triangle, above row numbers" is the name box, and that
doesn't select the whole sheet.
On my version, to select the whole sheet one needs to click in the square in
the top left-hand corner (above the row 1 and to the left of the column A
labels), or alternatively Control A will select the whole sheet.

 

[Guest]

I did as you instructed. But it messed that sheet up totally. All I want to
copy over is the conditional formatting RULES, not the formatting itself.

Any other ideas?

Chad
**************************

 

[Guest]

I believe he was referring to Excel 2007 which is what I'm using but I don't
think I specified that.
Chad
****************

 

[Ragdyer]

This goes for more then just CF.

Create and format SheetA exactly as you want your other sheets to duplicate
.... of course, minus the actual data.

With the SheetA tab selected,
hold down <Ctrl>,
click and drag the SheetA tab to the right or left,
release the mouse *first*, then the <Ctrl>.

You now have an *exact* copy of the original sheet, with a "2" appended
after the name.
Rename it, and you're all set with an exact copy!

 

[Roger Govier]

Hi David

That was in XL2007, which the OP did mention in his first post.
You are quite right, in XL2003 it is the square above row 1 and left of
column A
In XL2003 you need to type Ctrl +A twice to select the sheet.

 

[Roger Govier]

Hi Chad

I only tested with 1 rule in place, and it worked fine.
I can't see why it would be any different for multiple rules.
In my case formatted G12:H17 with the Conditional format =$A$1=1 and
Fill colour Yellow.
I selected the whole sheet (either the triangle cell top left, or Ctrl +
A (once only required in XL2007))
Copy
Move to cell A1 of Sheet3>Paste Special>Formats
Type a 1 in A1, cells G12:H17 turn yellow.

Nothing gets altered/destroyed/messed up in Sheet2

 

[Guest]

I specifically do not want an exact copy. I specifically want the formatting
to be different than the other page, except for the conditional formatting
RULES.

All I want to copy over is the conditional formatting RULES, nothing else.

It seems that there is a disconnect in trying to describe and understanding
what I am wanting to accomplish but I know of no other way to express it
except maybe this way:

Very simple, nothing about the two pages are to be similar, except:
1. the conditional formatting rules.

consequently:

2. in the chance that a specific value in a cell on one page is the same as
a cell on the other page, that having the same conditional formatting rules
on both pages would color both cells the same color.

**************************

 

[Ragdyer]

The CF "rules" are actually formulas that equate to TRUE or FALSE, where the
return of TRUE will enable the formatting as prescribed in the CF.

Now ... are you looking to copy the formulas of the CF, *BUT NOT* the
formatting that was selected on the previous sheet?

So that say, the *same* condition in B2 on Sheet1 and Sheet2 will invoke
*different* formats (colors) on each individual sheet?

OR ... are you looking to exactly copy *only* the CF's, and not all the
other surrounding formats in/on the other neighboring cells?

 

[RagDyeR]

Then, if all your sheets are identically constructed, all you would need to
do is copy *just* the cells containing the CF to the same cells on the other
sheets.

You can right click in the target cells and choose "Paste Special",
"Formats",
OR
Use the "Format Painter" icon (yellow paint brush).

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

I believe I'm trying to do this:
OR ... are you looking to exactly copy *only* the CF's, and not all the
other surrounding formats in/on the other neighboring cells?

Chad
******************

 

[ragdyer]

Good Luck!

 

[Roger Govier]

Hi

Whilst I misunderstood your original post and thought that you wanted to
copy all formats (including Conditional Formats)
the principle of what I was saying still applies, and RD has given you
the instructions to just copy and paste only those cells where the CF
conditions apply.

You can apply the CF to multiple pages at the same time, if you first
copy the relevant range of cells from your source sheet, then group
however many other sheets you wish before doing a Paste Special>Values
to the required locations on the destination sheet.

If you are thinking that the formulae can be exported on their own to
other sheets, as far as I am aware this is not possible as a worksheet
method.

Whether it can be achieved through writing some VBA code I don't know,
but that would be beyond my particular VBA skills.