Copy Conditional formats only

redart

Board Regular
Joined
Oct 15, 2002
Messages
246
Office Version
  1. 2007
Platform
  1. Windows
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
There's a check box under Edit/Paste Special/All Except Borders, I think that's the closest
 
Upvote 0
It's numerous font sizes I need to preserve -otherwise a long, long editing job.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 !
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top