Need VBA method to "bake" conditional formatting, ie make it permanent

  • Re: Need VBA method to "bake" conditional formatting, ie make it permanent


    Yep. Start with the macro recording going through the steps of:


    Selecting the cell range
    Deleting all Conditional Formats
    Applying formats equal to what was applied in the CF condition.


    Afterwards, post your code and workbook (dummy data) for helping in refining the code.

  • Re: Need VBA method to "bake" conditional formatting, ie make it permanent


    Quote from AAE;614528

    Yep. Start with the macro recording going through the steps of:


    Selecting the cell range
    Deleting all Conditional Formats
    Applying formats equal to what was applied in the CF condition.


    Afterwards, post your code and workbook (dummy data) for helping in refining the code.


    I must have misunderstood you because this gave me code for deleting all the conditional formats and then setting arbitrary formatting. I need a way to copy the result of the conditional format to the cell format programattically, and cell by cell.


    Quote from mikerickson;614529

    What version of Excel are you using?


    2010

  • Re: Need VBA method to "bake" conditional formatting, ie make it permanent


    How generic does this need to be? If it only needs to work for a certain sheet, you're likely better off going by the route of removing all conditional formating, then re-creating the fromating by testing for the conditions that you've just deleted in VBA, which only works if you know the conditions you'll be creating in advance. I've no idea how you'd go about creating a generic 'Conditional Formating Baker'; I suspect that if a google search doesn't turn up something some guru has created already, it's likely far harder than most of us will happily do for free.

  • Re: Need VBA method to "bake" conditional formatting, ie make it permanent


    It does need to be generic. Now that you've replied I've realized how I'm going to do this, and it will be easy enough that I'm amazed this hasn't been published already. I plan to post it back here once I get it working, but in case I forget, here's the outline I have in mind:


    My VBA code will read the existing conditional formatting rule on the cell. It will test the cell to see if the condition was matched. If it was, it will apply the formatting.


    Luckily with some googling I am halfway there, since someone has developed a method for reading cell color regardless of whether it was set by cell formatting or conditional formatting: http://www.vbaexpress.com/kb/getarticle.php?kb_id=190


    I will report back.

  • Re: Need VBA method to "bake" conditional formatting, ie make it permanent


    Have you had any success with this? All the code I have found to do this (and some of it I have used), only works with Excel up through 2003. Excel 2007 and 2010 introduced some major changes in the way Excel handles Conditional Formatting. My experience is that what worked in 2003 may work sometimes in 2007 or 2010 but then may not work at other times. I don't think we can count on older code to handle Conditional Formatting will work in the latest Excel versions.

  • Re: Need VBA method to "bake" conditional formatting, ie make it permanent


    Well the whole point of conditional formatting is to change when you apply changes to a cell. If you don't want that to happen, I suggest you just check the whole range, and change the background using "If -> then" or select case.

  • Re: Need VBA method to "bake" conditional formatting, ie make it permanent


    Your reply was of no help to this thread at all. My application that I was referring to needed to freeze the Conditional Format at a certain point in a second worksheet. Simply copying the worksheet only copied the Conditional Formatting so it could/would change undesirably if someone made a change to the second worksheet. I had to use VBA to freeze the Conditional Formatting to prevent those changes. The program works well in Excel 2003 but the major changes in 2007 and 2010 breaks this code. There seems to be no one who has worked out the same functionality for 2007 or 2010 yet.


    I understand that Conditional Formatting is primarily a UI tool, not a data analysis tool. But because it is a very good tool for UI, users will use it to help them analyze data. Once they have the analysis they are looking for they often need to lock that down in some way. But with Conditional Formatting that "lock down" is no easy to do. Just by saying we need to duplicate the formatting conditions in code to do the lock down does not solve the problem.


    Plus, Excel is used more for the UI than for data analysis.


    Again I ask "malathion" if they have had any success in their efforts to solve the problem they were posting about. If so there are many of us who would value any results they have.

  • Re: Need VBA method to "bake" conditional formatting, ie make it permanent


    There are CF conditions that didn't exist before 2007, like "top ten".
    And there are arrows and such that only can be added to a cell with CF. ("I have a formula in a cell. If that number is >10 I want it to show a red up arrow" is not solvable without CF, but can't be replicated without it, even it the formula is changed to a constant.)


    Would it be acceptable to restrict the Un-Conditialize-ing format feature to only those CF determined by formula?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!