VBA to Remove Conditional Formats, but Not the Results

  • Hello,


    I have the following code (as a module) in Excel 2010 that creates a .xlsx static copy of 3 specific sheets of large macro-enabled workbook (that I can't share here) with a button click. The code also removes certain objects, all hyperlinks, converts all formulas to just values, removes all data validation, and breaks all external workbook links. The only other thing I want the code to do is to keep the effects of any conditional formatting but remove the conditions.

    I've tried incorporating the following into different places of my code, but I can't seem to get anything to work.



    Running Allen Wyatt's code as an independent module seems to have the most promise, however, when I plug in a


    Call PasteFC()


    before the


    Next ws


    line of my code, I get an error 13 type mismatch in the last case (the Expression case) of the ActiveCondition function at line:


    If Application.Evaluate(FC.Formula1) Then.


    I've also tried the responses in:



    and



    Since Wyatt Allen's code seems the closest to accomplishing what I'm after, is the error 13 the easiest to resolve? Or are there other, better venues?


    Thanks in advance.

  • Hello,


    Have you tested the following


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • You are welcome:)


    Will take a look at what is so specific about ' cell border conditioning ' ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re,


    Hope Version 2 will be in line with your expectations ...


    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Just discovered borders properties have to be managed individually ...


    Since the number of potential combinations is quite enormous ... could you post a small sample file to illustrate the Borders you are actually using ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim,


    *In my case* here is the border situation:


    The cell (or merged cells) will have a thick box border by default; I do not have any conditional formatting that affects borders between cells. My conditional formatting (if the condition is met) will remove the left border, the top border, and the right border. *Sometimes* the bottom border will also be removed (if the condition is met), but more often than not, it will remain. I never change border color or style. I can put together a file if it will help, but it will have to wait until morning.

  • Hello,


    Thanks for the sample file.


    But, to my surprise ... not a single Conditional Formatting rule ...8|:!:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for your latest sample file


    It will allow to perform some tests which will be customized to your specific situation ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Let me quickly recap what must be actually tested for each cell :


    1. the Numbers of Borders :

    Only 3 possible cases : 4 Borders( always around), 1 (always Bottom) , 0 (none)

    2. the Borders Line Style :

    Only 1 possible case : Continuous

    3. the Borders Weight :

    Only 1 possible case : Thick

    4. the Borders Color :

    Only 1 possible case : Black


    Thanks for confirming above characteristics ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks a lot for your confirmation


    Will now dive into the intricacies of ' Conditionally Formatted Cell Borders ' ... 8o

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi,


    Attached is your Test file to deal your specifics regarding Conditional Formatting Borders ...


    Hope we are heading in the right direction ;)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello,


    When you say ' run the code ' ....


    Are you talking about the Test file ... or about your real-life worksheet ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I copy-pasted the code from the test file in your post into the code in my file and ran the sub:

  • Re,


    Just out of curiosity ... Have you tried to run the macro in the Test file attached in message # 16 ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)