Make conditional formatting static

Question

Is there any way to convert conditional formatting to static formatting in Excel?

I'm trying to export a range of a Excel Sheet to a new Workbook, with identical appearance but no formulas, links, etc. The problem here is that I have conditional formatting that relies on calculations outside exported range.

I've tried saving the workbook to .html, oddly enough the formatting shows in IE but not when reopening it in Excel.

Answer 1

I am suggesting you a much easier approach which will work all the time. I also tried hard with VBA but it was so difficult that I left in middle.

To convert conditional formatting to static,we will first convert Excel to Html(webpage) and then back to excel. Please follow below approach.

1. Load the workbook that contains your conditional formatting.
2. Save the workbook as an HTML file(as webpage). 
(Press F12, specify the HTML format, and give the workbook a different name.)
3. Restart Excel.
4. Load into Excel the HTML file you saved in step 2.
5. Save the workbook as an Excel workbook. 
(Press F12, specify an Excel workbook format, and give the workbook a different name.)

In the process of saving the Excel workbook in HTML format, the program "strips" all the conditional formatting and makes it explicit (absolute). You should be aware, however, that this process also does the same with your formulas, saving everything as a value, instead.

Answer 2

This approach seems to work well. I've only implemented it for background colours.

Sub FixColor()
    Dim r
    For Each r In Selection
        r.Interior.Color = r.DisplayFormat.Interior.Color
    Next r
    Selection.FormatConditions.Delete
End Sub