Make conditional formatting (font color, number format) static

[alice2011]

Dear all,


I just want to make conditional formatting 'static' by removing these 'hidden formulas'. I searched on the internet for some macro, there are some which does work to make colors permanent. But in my case I also want to make the number format permanent (e.g. number in % format).


Do you have any macro which can really make everything same as before, but just to remove the conditional formatting? A sample file attached.


Many thanks indeed.

[westconn1]

if you only want the numberformats that is relatively easy, but if you need the text formatting and colour as well, it would have many properties to determine the values and set to cell format
properties available to conditional formatting have changed over various versions of excel, can you specify a specific list of the properties you would need to assign from conditional to static formatting?

[snb]

Have a look at the 'styles' in Excel.

[alice2011]

Hi, westcomm1,

I'd like to have the two properties of 1) font color (e.g. blue color), 2) number formatting (e.g. %).

Hope it is possible to implement.

[SamT]

Select the column, right click it, choose "Format Cells," on the "Number" tab, look at "Percentage."

If you want the entire Column to be in blue font, on the "Font" tab, look at the Drop Down "Color."

In fact, while you are in the Format Cells dialog, look at all the tabs and choices. Play with them. See what they do.

[alice2011]

Hi SamT. Thanks for your approach. Your approach is manual, or extra steps. For my case, I just want to retain the original formatting, rather than take extra work to do it manually (impossible to do it manually as there are many tabs/columns to format.

[snb]

I can't say it's very clear to me what you are after (and what you are not....)

In your case I'd use:

Sub M_snb()
   On Error Resume Next

   For Each cl In Cells.SpecialCells(xlCellTypeAllFormatConditions)
       cl.SpecialCells(xlCellTypeSameFormatConditions).FormatConditions(1).Delete
   Next
End Sub

[SamT]

Now I am confused about what you want.

Return cells to original format
Format percentage type cells to permanent blue percentages
???????????????

Are you trying to keep the appearance of all the Cells the way it looks with conditional formatting, while not using conditional formatting?