+ Reply to Thread
Results 1 to 10 of 10

VBA to Export and Re-Import Conditional Formatting Rules

  1. #1
    Registered User
    Join Date
    11-19-2018
    Location
    New York
    MS-Off Ver
    2016
    Posts
    1

    VBA to Export and Re-Import Conditional Formatting Rules

    Hey Everyone,

    I am not sure if this is possible to do in VBA but could use some guidance. I have a Macro Enabled workbook A that contains hundreds of conditional formatting rules across several tabs. This workbook serves as a template. I am trying to find a way to export or print out all the conditional formatting rules including formula and named range references, range of cells the rules apply too, color and font etc ) to a separate worksheet which can than be copied and pasted into a new workbook and have a macro re-import all the conditional formatting rules by referencing this output sheet.

    I can't post links yet but Extend Office has post on how to "Print all Conditional Formatting Rules" to a list but it does not account account for rules if the cell is blank, or include any formatting such as the color, font, boarder etc and also only does one tab at a time. I'd like to be able to export all the rules for the entire workbook and display all the possible formatting rules and re-import these to a operate workbook.

    Appreciate any help

  2. #2
    Registered User
    Join Date
    12-19-2018
    Location
    Antibes, France
    MS-Off Ver
    2016
    Posts
    4

    Re: VBA to Export and Re-Import Conditional Formatting Rules

    Hi Pepe Le Mokko,
    Thank you for your explanation and messages clean up.
    I will first try to make this tool Excel 64 bits compatible and then follow your tips.
    And if you agree with your help, verify it also works on Excel Linux.
    JP
    Last edited by Balbox; 04-03-2020 at 12:53 PM.

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,585

    Re: VBA to Export and Re-Import Conditional Formatting Rules

    As it is VBA I doubt it will work on Linux. Play on Linux does not import the necessary libraries ( or whatever)
    Anyway, I will give it a try

  4. #4
    Registered User
    Join Date
    12-19-2018
    Location
    Antibes, France
    MS-Off Ver
    2016
    Posts
    4

    Re: VBA to Export and Re-Import Conditional Formatting Rules

    Hello,

    Here is a version compatible with Excel 64 bits which also improves the display of cell format and its related FC formats when typing [Ctrl + w].

    JP
    Attached Files Attached Files
    Last edited by Balbox; 04-12-2020 at 11:38 PM.

  5. #5
    Registered User
    Join Date
    12-19-2018
    Location
    Antibes, France
    MS-Off Ver
    2016
    Posts
    4

    Re: VBA to Export and Re-Import Conditional Formatting Rules

    Ohhh ! Ok, the site does not take the selected file name to upload it; On have also to click on a button which is completely on the other side of the page !
    This is for selecting high university degree members only ?
    Anyway, with your help I eventually managed to upload the file
    Thank you
    Last edited by Balbox; 04-11-2020 at 08:16 AM.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,648

    Re: VBA to Export and Re-Import Conditional Formatting Rules

    Woo-hoo! I will tidy up the thread shortly.

    EDIT: Thread cleaned up.
    Last edited by AliGW; 04-11-2020 at 08:25 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  7. #7
    Registered User
    Join Date
    12-19-2018
    Location
    Antibes, France
    MS-Off Ver
    2016
    Posts
    4

    Re: VBA to Export and Re-Import Conditional Formatting Rules

    Here is V1.8 that fixes a bug on FC Priority variable type.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-03-2021
    Location
    Canada
    MS-Off Ver
    10
    Posts
    2

    Re: VBA to Export and Re-Import Conditional Formatting Rules

    Thank you Balbox. Your solution is very helpfull. This is after almost two years from posting!

  9. #9
    Registered User
    Join Date
    03-03-2021
    Location
    Canada
    MS-Off Ver
    10
    Posts
    2

    Re: VBA to Export and Re-Import Conditional Formatting Rules

    Quote Originally Posted by Balbox View Post
    Here is V1.8 that fixes a bug on FC Priority variable type.
    Hi Balbox,
    How can I use your solution to copy all CF from one workbook and paste the same into several other workbooks in one go?

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,911

    Re: VBA to Export and Re-Import Conditional Formatting Rules

    Quote Originally Posted by zakn View Post
    Hi Balbox,
    How can I use your solution to copy all CF from one workbook and paste the same into several other workbooks in one go?
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Formatting Rules
    By LeePipe in forum Outlook Formatting & Functions
    Replies: 2
    Last Post: 08-14-2017, 04:02 PM
  2. [SOLVED] Conditional formatting with more than 15 rules
    By lnjkbsn in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-26-2016, 05:01 AM
  3. [SOLVED] Conditional Formatting - 2 rules in 1
    By Leathermyth in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-19-2015, 02:29 PM
  4. [SOLVED] Conditional Formatting Rules
    By GoGoeGo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-10-2014, 01:48 PM
  5. Conditional formatting rules
    By puzzlelover22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-01-2014, 12:45 PM
  6. conditional formatting rules for a cell on sheet 1 based on rules from sheet 2
    By jsard in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 08-15-2013, 09:22 AM
  7. Conditional Formatting with more than 3 rules
    By vickyho1008 in forum Excel General
    Replies: 5
    Last Post: 07-24-2008, 10:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1