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.

score 8 · Accepted Answer · answered Jan 14 '11 at 16:52

The following idea was taken from here, although modified to fit some new conditional formatting structures and your needs.

It works like this: Given a workbook with some conditional formatting (make a copy of yours), you put in Sub a() the range of cells you want to transform from conditional to straight formatting, and run the macro. After that, just delete manually the conditional formats, and presto!

Sorry about the code length ... life is sometimes like this :(

Option Explicit
Sub a()

Dim iconditionno As Integer
Dim rng, rgeCell As Range
Set rng = Range("A1:A10")

For Each rgeCell In rng

   If rgeCell.FormatConditions.Count <> 0 Then
       iconditionno = ConditionNo(rgeCell)
       If iconditionno <> 0 Then
           rgeCell.Interior.ColorIndex = rgeCell.FormatConditions(iconditionno).Interior.ColorIndex
           rgeCell.Font.ColorIndex = rgeCell.FormatConditions(iconditionno).Font.ColorIndex
       End If
   End If
Next rgeCell

End Sub
Private Function ConditionNo(ByVal rgeCell As Range) As Integer

Dim iconditionscount As Integer
Dim objFormatCondition As FormatCondition

    For iconditionscount = 1 To rgeCell.FormatConditions.Count
        Set objFormatCondition = rgeCell.FormatConditions(iconditionscount)
        Select Case objFormatCondition.Type
           Case xlCellValue
               Select Case objFormatCondition.Operator
                   Case xlBetween: If Compare(rgeCell.Value, ">=", objFormatCondition.Formula1) = True And _
                                           Compare(rgeCell.Value, "<=", objFormatCondition.Formula2) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlNotBetween: If Compare(rgeCell.Value, "<=", objFormatCondition.Formula1) = True And _
                                           Compare(rgeCell.Value, ">=", objFormatCondition.Formula2) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlGreater: If Compare(rgeCell.Value, ">", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlEqual: If Compare(rgeCell.Value, "=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlGreaterEqual: If Compare(rgeCell.Value, ">=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlLess: If Compare(rgeCell.Value, "<", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlLessEqual: If Compare(rgeCell.Value, "<=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlNotEqual: If Compare(rgeCell.Value, "<>", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                  If ConditionNo > 0 Then Exit Function
              End Select

          Case xlExpression
            If Application.Evaluate(objFormatCondition.Formula1) Then
               ConditionNo = iconditionscount
               Exit Function
            End If
       End Select

    Next iconditionscount
End Function

Private Function Compare(ByVal vValue1 As Variant, _
                         ByVal sOperator As String, _
                         ByVal vValue2 As Variant) As Boolean

   If Left(CStr(vValue1), 1) = "=" Then vValue1 = Application.Evaluate(vValue1)
   If Left(CStr(vValue2), 1) = "=" Then vValue2 = Application.Evaluate(vValue2)

   If IsNumeric(vValue1) = True Then vValue1 = CDbl(vValue1)
   If IsNumeric(vValue2) = True Then vValue2 = CDbl(vValue2)

   Select Case sOperator
      Case "=": Compare = (vValue1 = vValue2)
      Case "<": Compare = (vValue1 < vValue2)
      Case "<=": Compare = (vValue1 <= vValue2)
      Case ">": Compare = (vValue1 > vValue2)
      Case ">=": Compare = (vValue1 >= vValue2)
      Case "<>": Compare = (vValue1 <> vValue2)
   End Select
End Function

Thanks, I'll try that on Monday. Pretty insane that there's no .isActive property on FormatCondition (or a direct way to get the computed format of a cell). — Martin, Jan 15 '11 at 20:18
@Martin You should check first is @Chris' answer is not enough. It's easier (but you are forced to delete all your formulas in the range) — Dr. belisarius, Jan 15 '11 at 20:30
I ended up simply changing my conditionals to only link to cells inside the area I want to export, thus they should continue to work. Your answer is the generic solution to this problem, so I selected it as the correct answer. — Martin, Jan 18 '11 at 19:44
I am noticing that none of the solutions here seem to work if the conditional format was set using a formula. -_- ugh — Lorenzo, Apr 09 '14 at 04:04

score 4 · Answer 2 · answered Oct 09 '18 at 14:57

4

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

answered Oct 09 '18 at 14:57

pooroldpedro

181
1
4

1

This is perfect, a much simpler solution than the other solutions in modern Excel! Easily adaptable to other properties as well if you need to, note that it will also delete merged cells in the range – Chris Schaller Sep 20 '20 at 13:01

score 4 · Answer 3 · answered Jun 08 '20 at 17:15

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.

You might be able to copy from the HTML browser and paste->special just the formatting. If that doesn't work, copy from the browser and paste into an empty excel sheet and from there copy and paste the formatting. — jlear, Jun 08 '20 at 17:55
@jlear excel sets `display: none` on overflowed text. caution when copying it — Monday Fatigue, Jan 05 '22 at 12:22

Chris Rae · Answer 4 · 2015-10-13T22:17:51.780

3

I hate it when people say "hey, why aren't you doing that whole thing this other way", but I'll just throw it out there: when I've wanted to do this in the past, I've done it by first copying the entire worksheet in question and then copying and pasting the formulas as values (without moving their location at all). This will freeze the conditional formatting obviously, but also means that recalculating the workbook won't leave you with values that are no longer appropriate for the formatting that's sitting on them.

If this doesn't work, belisarius' code looks great.

edited Oct 13 '15 at 22:17

answered Jan 14 '11 at 18:50

Chris Rae

5,627
2
36
51

Not bad at all if it doesn't violate other OP's needs. +1 – Dr. belisarius Jan 14 '11 at 19:10
1

Thx - copying values is part of the plan. One problem here is that I need only a portion of the sheet and delete the other rows/columns, but the condition links to cells outside that portion. Another (minor) problem with copy values is that it kills any sub-cell formatting in text cells. – Martin Jan 15 '11 at 20:40
I just tried this in **Excel 2010** and it doesn't seem to work. Can you detail the steps you take to copy a range of cells with conditional formatting to a different worksheet. When I paste *values*, the formatting is lost completely, when I paste *values and source formatting*, the `conditional formatting` rules are also copied to the new sheet :(. – Marcus Mangelsdorf Oct 13 '15 at 17:58
Sorry, actually - I didn't make this very clear. I've edited the answer. – Chris Rae Oct 13 '15 at 22:16

score 1 · Answer 5 · answered Oct 01 '14 at 17:36

I've put together Belisarius and Cameron Forward's addition. You have to select the area you would like to freeze (large selections might take a while). I've noticed if there are excel errors on cells it might cause an exception, but otherwise this is working great on Excel 2010. By the way, thank you all!

Option Explicit

Sub FreezeConditionalFormattingOnSelection()
    Call FreezeConditionalFormatting(Selection)
    Selection.FormatConditions.Delete
End Sub

Public Function FreezeConditionalFormatting(Rng As Range)
Rem Originally posted by http://stackoverflow.com/users/353410/belisarius
Rem at http://stackoverflow.com/questions/4692918/excel-make-conditional-formatting-static
Rem Modified 2012-04-20 by gcl to:
Rem   (a) be a function taking target range as an argument, and
Rem   (b) to cancel any multiple selection before processing in order to work around a bug
Rem         in Excel 2003 wherein querying the formula on any cell in a multiple/extended selection
Rem         returns the conditional formatting on the first cell in that selection!
Rem   (c) return number of cells that it modified.

Dim iconditionno As Integer
Dim rgeCell As Range
Dim nCFCells As Integer
Dim rgeOldSelection As Range

Set rgeOldSelection = Selection 'new

nCFCells = 0
For Each rgeCell In Rng
    rgeCell.Select  'new

   If rgeCell.FormatConditions.Count <> 0 Then
       iconditionno = ConditionNo(rgeCell)
       If iconditionno <> 0 Then
           rgeCell.Interior.ColorIndex = rgeCell.FormatConditions(iconditionno).Interior.ColorIndex

           rgeCell.Font.ColorIndex = rgeCell.FormatConditions(iconditionno).Font.ColorIndex
           nCFCells = nCFCells + 1
       End If
   End If
Next rgeCell

rgeOldSelection.Select 'new

FreezeConditionalFormatting = nCFCells
End Function

Private Function ConditionNo(ByVal rgeCell As Range) As Integer
Rem posted by http://stackoverflow.com/users/353410/belisarius
Rem at http://stackoverflow.com/questions/4692918/excel-make-conditional-formatting-static

Dim iconditionscount As Integer
Dim objFormatCondition As FormatCondition
Dim f3 As String

    For iconditionscount = 1 To rgeCell.FormatConditions.Count
        Set objFormatCondition = rgeCell.FormatConditions(iconditionscount)
        Select Case objFormatCondition.Type
           Case xlCellValue
               Select Case objFormatCondition.Operator
                   Case xlBetween: If Compare(rgeCell.Value, ">=", objFormatCondition.Formula1) = True And _
                                           Compare(rgeCell.Value, "<=", objFormatCondition.Formula2) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlNotBetween: If Compare(rgeCell.Value, "<=", objFormatCondition.Formula1) = True And _
                                           Compare(rgeCell.Value, ">=", objFormatCondition.Formula2) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlGreater: If Compare(rgeCell.Value, ">", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlEqual: If Compare(rgeCell.Value, "=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlGreaterEqual: If Compare(rgeCell.Value, ">=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlLess: If Compare(rgeCell.Value, "<", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlLessEqual: If Compare(rgeCell.Value, "<=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlNotEqual: If Compare(rgeCell.Value, "<>", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                  If ConditionNo > 0 Then Exit Function
              End Select

          Case xlExpression

            f3 = objFormatCondition.Formula1
            f3 = Application.ConvertFormula(Formula:=f3, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1, RelativeTo:=objFormatCondition.AppliesTo.Cells(1, 1))
            f3 = Application.ConvertFormula(Formula:=f3, FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlR1C1, ToAbsolute:=xlAbsolute, RelativeTo:=rgeCell)
            f3 = Application.ConvertFormula(Formula:=f3, FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlA1)

            If Application.Evaluate(f3) Then
               ConditionNo = iconditionscount
               Exit Function
            End If
       End Select

    Next iconditionscount
End Function

Private Function Compare(ByVal vValue1 As Variant, _
                         ByVal sOperator As String, _
                         ByVal vValue2 As Variant) As Boolean

   If Left(CStr(vValue1), 1) = "=" Then vValue1 = Application.Evaluate(vValue1)
   If Left(CStr(vValue2), 1) = "=" Then vValue2 = Application.Evaluate(vValue2)

   If IsNumeric(vValue1) = True Then vValue1 = CDbl(vValue1)
   If IsNumeric(vValue2) = True Then vValue2 = CDbl(vValue2)

   Select Case sOperator
      Case "=": Compare = (vValue1 = vValue2)
      Case "<": Compare = (vValue1 < vValue2)
      Case "<=": Compare = (vValue1 <= vValue2)
      Case ">": Compare = (vValue1 > vValue2)
      Case ">=": Compare = (vValue1 >= vValue2)
      Case "<>": Compare = (vValue1 <> vValue2)
   End Select
End Function

score 0 · Answer 6 · answered Apr 20 '12 at 19:31

Thanks to Belisarius for the very useful answer! However, it runs into a bug in Excel 2003 where querying the conditional formatting formula on any cell in a multiple/extended selection returns the formula for the first cell in that selection! To work around this I had to cancel any selection at the beginning and restore it at the end. I also changed his subroutine into a function that takes a range and returns the number of cells modified, and added a wrapper subroutine that applies it to the current selection and deletes any conditional formatting (since it's no longer needed), so you no longer need to modify it to hard-code your target range.

Option Explicit

Sub FreezeConditionalFormattingOnSelection()
    Call FreezeConditionalFormatting(Selection)
    Selection.FormatConditions.Delete
End Sub

Public Function FreezeConditionalFormatting(rng As Range)
Rem Originally posted by http://stackoverflow.com/users/353410/belisarius
Rem at http://stackoverflow.com/questions/4692918/excel-make-conditional-formatting-static
Rem Modified 2012-04-20 by gcl to:
Rem   (a) be a function taking target range as an argument, and
Rem   (b) to cancel any multiple selection before processing in order to work around a bug
Rem         in Excel 2003 wherein querying the formula on any cell in a multiple/extended selection
Rem         returns the conditional formatting on the first cell in that selection!
Rem   (c) return number of cells that it modified.

Dim iconditionno As Integer
Dim rgeCell As Range
Dim nCFCells As Integer
Dim rgeOldSelection As Range

Set rgeOldSelection = Selection 'new

nCFCells = 0
For Each rgeCell In rng
    rgeCell.Select  'new

   If rgeCell.FormatConditions.Count <> 0 Then
       iconditionno = ConditionNo(rgeCell)
       If iconditionno <> 0 Then
           rgeCell.Interior.ColorIndex = rgeCell.FormatConditions(iconditionno).Interior.ColorIndex
           rgeCell.Font.ColorIndex = rgeCell.FormatConditions(iconditionno).Font.ColorIndex
           nCFCells = nCFCells + 1
       End If
   End If
Next rgeCell

rgeOldSelection.Select 'new

FreezeConditionalFormatting = nCFCells
End Function

Private Function ConditionNo(ByVal rgeCell As Range) As Integer
Rem posted by http://stackoverflow.com/users/353410/belisarius
Rem at http://stackoverflow.com/questions/4692918/excel-make-conditional-formatting-static

Dim iconditionscount As Integer
Dim objFormatCondition As FormatCondition

    For iconditionscount = 1 To rgeCell.FormatConditions.Count
        Set objFormatCondition = rgeCell.FormatConditions(iconditionscount)
        Select Case objFormatCondition.Type
           Case xlCellValue
               Select Case objFormatCondition.Operator
                   Case xlBetween: If Compare(rgeCell.Value, ">=", objFormatCondition.Formula1) = True And _
                                           Compare(rgeCell.Value, "<=", objFormatCondition.Formula2) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlNotBetween: If Compare(rgeCell.Value, "<=", objFormatCondition.Formula1) = True And _
                                           Compare(rgeCell.Value, ">=", objFormatCondition.Formula2) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlGreater: If Compare(rgeCell.Value, ">", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlEqual: If Compare(rgeCell.Value, "=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlGreaterEqual: If Compare(rgeCell.Value, ">=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlLess: If Compare(rgeCell.Value, "<", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlLessEqual: If Compare(rgeCell.Value, "<=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlNotEqual: If Compare(rgeCell.Value, "<>", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                  If ConditionNo > 0 Then Exit Function
              End Select

          Case xlExpression
            If Application.Evaluate(objFormatCondition.Formula1) Then
               ConditionNo = iconditionscount
               Exit Function
            End If
       End Select

    Next iconditionscount
End Function

Private Function Compare(ByVal vValue1 As Variant, _
                         ByVal sOperator As String, _
                         ByVal vValue2 As Variant) As Boolean

   If Left(CStr(vValue1), 1) = "=" Then vValue1 = Application.Evaluate(vValue1)
   If Left(CStr(vValue2), 1) = "=" Then vValue2 = Application.Evaluate(vValue2)

   If IsNumeric(vValue1) = True Then vValue1 = CDbl(vValue1)
   If IsNumeric(vValue2) = True Then vValue2 = CDbl(vValue2)

   Select Case sOperator
      Case "=": Compare = (vValue1 = vValue2)
      Case "<": Compare = (vValue1 < vValue2)
      Case "<=": Compare = (vValue1 <= vValue2)
      Case ">": Compare = (vValue1 > vValue2)
      Case ">=": Compare = (vValue1 >= vValue2)
      Case "<>": Compare = (vValue1 <> vValue2)
   End Select
End Function

score 0 · Answer 7 · answered Jan 16 '14 at 11:05

I picked up this addition over at excel.tips.com to make this work for Excel 2010 and adapted it for gcl's version of Belisarius' post. Substitute this line under the xlExpression Case:

If Application.Evaluate(objFormatCondition.Formula1) Then

With this:

f3 = objFormatCondition.Formula1
f3 = Application.ConvertFormula(Formula:=f3, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1, RelativeTo:=objFormatCondition.AppliesTo.Cells(1, 1))
f3 = Application.ConvertFormula(Formula:=f3, FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlR1C1, ToAbsolute:=xlAbsolute, RelativeTo:=rgeCell)
f3 = Application.ConvertFormula(Formula:=f3, FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlA1)
If Application.Evaluate(f3) Then

It makes the formula propogate down and across correctly.

I keep getting a "variable not defined" error when I do this. — Lorenzo, Apr 09 '14 at 03:46
@Lorenzo: You will have to add `Dim f3 As String` to the beginning of the the code to define the f3 variable. Also note, that you will have to "translate" the formula coming from the conditional formatting (CF) if you use a non-English locale of Excel. (See [this answer](http://stackoverflow.com/a/13254521/2822719) for how to translate) — Marcus Mangelsdorf, Oct 13 '15 at 18:28

Make conditional formatting static

7 Answers7

Linked