Results 1 to 10 of 10

Thread: Copy Conditional formats only
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular redart's Avatar
    Join Date
    Oct 2002
    Location
    Spain
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    May 2002
    Posts
    890
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There's a check box under Edit/Paste Special/All Except Borders, I think that's the closest

  3. #3
    Board Regular redart's Avatar
    Join Date
    Oct 2002
    Location
    Spain
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It's numerous font sizes I need to preserve -otherwise a long, long editing job.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Scotland
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular redart's Avatar
    Join Date
    Oct 2002
    Location
    Spain
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    New Member
    Join Date
    Aug 2007
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I may have found an answer to your question (and mine as well .
    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 !

  8. #8
    New Member
    Join Date
    Oct 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Conditional formats only

    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.

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Conditional formats only

    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 by JugglerJAF; Oct 1st, 2018 at 12:29 PM.
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

  10. #10
    New Member
    Join Date
    Oct 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Conditional formats only

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •