Copy Conditional formats only

redart

Board Regular
Joined
Oct 15, 2002
Messages
239
Anyone know if it's possible to copy and paste ONLY conditional formats, without copying regular formats?. Using the format painter lifts everything, but I need to preserve regular (toolbar) formatting, whilst copying the conditional formats.

Thanks, Tony
 

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890
There's a check box under Edit/Paste Special/All Except Borders, I think that's the closest
 

redart

Board Regular
Joined
Oct 15, 2002
Messages
239
It's numerous font sizes I need to preserve -otherwise a long, long editing job.
 

rmtaylor

Board Regular
Joined
Feb 17, 2002
Messages
155
To Copy the formatting down select the row where the condition is applies say A1:A10
click on copy .
Select area where the formatting is to be copied to,
A2:E20 say.
Click on EDIT, PASTE SPECIAL then select FORMATTING.

The conditional formatting will be active in this array of cells

NB The cells absolute values must be set set as you require, before attempting to paste formatting
 

redart

Board Regular
Joined
Oct 15, 2002
Messages
239
Thanks for the replies...but I still have the problem that using these methods copies ALL the formats from the donor to the destination cells. With Excel having a limit of 3 conditional formats allowed, I had to manually format all the cells which required font 6, from the default of font 9. I need to keep the font 6 cells at font 6, and the font 9 cells at font 9, whilst painting 3 conditional color formats across the whole lot. Whichever cell I copy from, I end up with ALL cells the same font as the original.
 

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
912
You could write a macro to create your conditional formats over whatever is selected and then select the range to be formatted and run the macro.
 

emichiel

New Member
Joined
Aug 2, 2007
Messages
1
I may have found an answer to your question (and mine as well :cool: .
1. Prepare 2 columns or rows with the data and formatting you want.
2. Define your conditional format for 1 cell
3. Copy and Paste Special/Formats to the other cells in the same column/row. Of course, all these cells are now in completely the same format as the conditional formatted cell. BUT :
4. Copy the complete adjacant column/row and Paste Special/Formulas and number formats.

As such, the conditional formats are maintained, as well as the other formats !

At your service !
 

BELewis

New Member
Joined
Oct 1, 2018
Messages
2
I had this issue, but I figured out an easy solution for my circumstance. I needed to copy the conditional formatting for a column. So I used format painter to place the formatting in an individual cell that was not being used in the column. Then I opened the conditional formatting menu and changed the range for all the rules to include the whole column. Hope this helps.
 

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
264
Rather than copying and pasting, if you go into the conditional format and choose Manage Rules, you can set multiple ranges to use the same rules as the original range. Just use Ctrl when selecting the second range to keep both ranges.

Say your original conditional formatting was for cells B2 to B21. In the "Applies to" box when you select manage rules, this would appear as =$B$2:$B$21

If you wanted to apply the same conditional formatting to F2 to F21 as well, simply re-select the ranges so the box reads =$B$2:$B$21,$F$2:$F$21
 
Last edited:

BELewis

New Member
Joined
Oct 1, 2018
Messages
2
Thank you for your replay.

I realized that I was unclear in my previous post.

I was looking for an answer for how to copy and paste the conditional formatting from one sheet to another sheet, and my response was for that situation.

In this case, I needed to transfer the formatting. So, I copied and pasted a cell with the formatting to the new workbook and then as you said changed the range that the formatting applied to in the new workbook.

However, if you are simply moving the conditional formatting around worksheet, you are correct that no copying and pasting are necessary.

Thanks
 

Forum statistics

Threads
1,077,855
Messages
5,336,780
Members
399,102
Latest member
chudson1

Some videos you may like

This Week's Hot Topics

Top