Removing Conditional Formats, but Not the Effects

by Allen Wyatt
(last updated June 3, 2016)

28

Charlie wondered if there is a way to "make permanent" the effects of conditional formatting at any given time. For instance, if a conditional format specifies that a particular cell be bold red type, then Charlie wanted a way to remove the conditional format and make the cell bold and red.

There is no intrinsic way to do this in Excel; none of the Paste Special options will do the task, as desired. You can, however, use a macro to accomplish the task:

Option Explicit
Sub PasteFC()
    Application.ScreenUpdating = False
    Dim rWhole As Range
    Dim rCell As Range
    Dim ndx As Integer
    Dim FCFont As Font
    Dim FCBorder As Border
    Dim FCInt As Interior
    Dim x As Integer
    Dim iBorders(3) As Integer

    iBorders(0) = xlLeft
    iBorders(1) = xlRight
    iBorders(2) = xlTop
    iBorders(3) = xlBottom

    Set rWhole = Selection

    For Each rCell In rWhole
        rCell.Select
        ndx = ActiveCondition(rCell)
        If ndx <> 0 Then
            'Change the Font info
            Set FCFont = rCell.FormatConditions(ndx).Font
            With rCell.Font
                .Bold = NewFC(.Bold, FCFont.Bold)
                .Italic = NewFC(.Italic, FCFont.Italic)
                .Underline = NewFC(.Underline, FCFont.Underline)
                .Strikethrough = NewFC(.Strikethrough, _
                  FCFont.Strikethrough)
                .ColorIndex = NewFC(.ColorIndex, FCFont.ColorIndex)
            End With
            'Change the Border Info for each of the 4 types
            For x = 0 To 3
                Set FCBorder = rCell.FormatConditions(ndx).Borders(iBorders(x))
                With rCell.Borders(iBorders(x))
                    .LineStyle = NewFC(.LineStyle, FCBorder.LineStyle)
                    .Weight = NewFC(.Weight, FCBorder.Weight)
                    .ColorIndex = NewFC(.ColorIndex, FCBorder.ColorIndex)
                End With
            Next x
            'Change the interior info
            Set FCInt = rCell.FormatConditions(ndx).Interior
            With rCell.Interior
                .ColorIndex = NewFC(.ColorIndex, FCInt.ColorIndex)
                .Pattern = NewFC(.Pattern, FCInt.Pattern)
            End With
            'Delete FC
            rCell.FormatConditions.Delete
        End If
    Next
    rWhole.Select
    Application.ScreenUpdating = True
    MsgBox ("The Formatting based on the Conditions" & vbCrLf & _
      "in the range " & rWhole.Address & vbCrLf & _
      "has been made standard for those cells" & vbCrLf & _
      "and the Conditional Formatting has been removed")
End Sub
Function NewFC(vCurrent As Variant, vNew As Variant)
    If IsNull(vNew) Then
        NewFC = vCurrent
    Else
        NewFC = vNew
    End If
End Function
Function ActiveCondition(rng As Range) As Integer
    'Chip Pearson http://www.cpearson.com/excel/CFColors.htm
    Dim ndx As Long
    Dim FC As FormatCondition

    If rng.FormatConditions.Count = 0 Then
        ActiveCondition = 0
    Else
    For ndx = 1 To rng.FormatConditions.Count
        Set FC = rng.FormatConditions(ndx)
        Select Case FC.Type
            Case xlCellValue
                Select Case FC.Operator
                    Case xlBetween
                        If CDbl(rng.Value) >= CDbl(FC.Formula1) And _
                          CDbl(rng.Value) <= CDbl(FC.Formula2) Then
                            ActiveCondition = ndx
                            Exit Function
                        End If
                    Case xlGreater
                        If CDbl(rng.Value) > CDbl(FC.Formula1) Then
                            ActiveCondition = ndx
                            Exit Function
                        End If
                    Case xlEqual
                        If CDbl(rng.Value) = CDbl(FC.Formula1) Then
                            ActiveCondition = ndx
                            Exit Function
                        End If
                    Case xlGreaterEqual
                        If CDbl(rng.Value) >= CDbl(FC.Formula1) Then
                            ActiveCondition = ndx
                            Exit Function
                        End If
                    Case xlLess
                        If CDbl(rng.Value) < CDbl(FC.Formula1) Then
                            ActiveCondition = ndx
                            Exit Function
                        End If
                    Case xlLessEqual
                        If CDbl(rng.Value) <= CDbl(FC.Formula1) Then
                            ActiveCondition = ndx
                            Exit Function
                        End If
                    Case xlNotEqual
                        If CDbl(rng.Value) <> CDbl(FC.Formula1) Then
                            ActiveCondition = ndx
                            Exit Function
                        End If
                    Case xlNotBetween
                        If CDbl(rng.Value) <= CDbl(FC.Formula1) Or _
                            CDbl(rng.Value) >= CDbl(FC.Formula2) Then
                            ActiveCondition = ndx
                            Exit Function
                        End If
                    Case Else
                        Debug.Print "UNKNOWN OPERATOR"
                End Select
            Case xlExpression
                If Application.Evaluate(FC.Formula1) Then
                    ActiveCondition = ndx
                    Exit Function
                End If
            Case Else
                Debug.Print "UNKNOWN TYPE"
        End Select
    Next ndx
    End If
    ActiveCondition = 0
End Function

There are three procedures in this solution. The last procedure, ActiveCondition, is designed to return a number indicating which of the conditions in a conditional format is currently in effect. This routine was found at Chip Pearson's site, as indicated in the first comment of the function. (No sense in re-inventing the wheel. :>))

The center function, NewFC, is simply used to determine which of two values is valid. The procedure you actually run, however, is PasteFC. Simply select the cells you want to convert to explicit formatting, then run the procedure. It checks each cell you selected for which formatting condition is active, determines the formatting of that condition, and then applies it to the cell. Finally, the conditional formatting for the cell is removed.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (1947) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Offering Options in a Macro

When creating macros, you often need to offer a series of choices to a user. This tip demonstrates how easy it is to offer ...

Discover More

Changing the Height of a Font

Scaling the width of a font is easy to do with Word's formatting capabilities. Scaling the height of the fonts is not so ...

Discover More

Defining Protected Sections as a Building Block

Building Blocks can be very useful in creating documents from standard parts and pieces. But what if the text you want to use ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

MORE EXCELTIPS (MENU)

Shading Rows with Conditional Formatting

If you need to shade alternating rows in a data table, you'll want to examine how you can accomplish the task with ...

Discover More

Understanding Conditional Formatting Conditions

Conditional formatting can be a great way to highlight specific information in your worksheets. This tip explains the ...

Discover More

Detecting Errors in Conditional Formatting Formulas

If an error exists in a formula tucked inside a conditional format, you may never know it is there. There are ways to find ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 + 8?

2016-09-04 06:11:56

Pasang Sherpa

Sir,
How can keep the original valve or text of the result that I create from conditioning rules?
for month I divide in 4 group stating Q1,Q2,Q3,Q4. ?
Each Q(quarter has 3 month (90days).

please email on me.
thanks in advance.


2016-08-15 16:58:29

jh

Forgot an important step on previous message!

Save your excel file as an HTML file.

Open the HTML file using excel.

Now save the HTML file as an excel file again.

Formatting will be there, but conditional formatting will be gone!


2016-08-15 16:29:36

jh

Save your excel file as a HTML file on your desktop.

Open the HTML file using excel.

Format colors will be there, but conditional formatting will be gone


2016-05-18 22:24:15

altoplano

Buzzed Aldrin's approach worked for me, too.


2016-05-11 10:18:15

Matt

Depending on the format you applied, you can filter on that format (filter by color, etc.) or otherwise limit the unhidden cells to the data set you are dealing with, then set the format. The conditional formatting was probably designed to help identify these items. Now that you have, just set it and forget it. Works with simple sets. More complex would be more....complex.


2016-01-08 13:06:17

PGandhi

I have excel 2007. This code worked only for some cells. Code seems to error out at
If Application.Evaluate(FC.Formula1)...

I have tried what Thorstein suggested, but wasn't successfull. I think I was doing something wrong.
Can someone help how to get past this?


2015-09-12 11:37:36

LuckyCharms

Buzzed Aldrin's solution worked for me. and is the far easiest way. Thanks


2015-04-29 16:24:52

Tolga

Is it possible to use this in Excel 2007 ?

I got mismatch error.


2014-09-17 21:22:16

Pete

How would you modify this to work when data is enter into a different cell. Eg I have a date in A2 that has traffic light conditional formatting and I would like the Conditional Formats removed but not the effects when a date is entered into the cell immediatly to the right (in this case A3).

Thanks


2014-04-13 02:56:19

Guido Massaro

Hello,

I tried the code you gave here.
But it comes back with an error nr: 13
Type do not match.
Case xlExpression
If Application.Evaluate(FC.Formula1) Then

Any help would be appreciated.
i use excel 2007


2014-03-01 02:01:23

Buzzed Aldrin

Yep, what Pawel said.

Copy/paste to Word and back to Excel appears to convert any kind (font, border, pattern) of conditional format to regular explicit format.

Also works for Excel 2003 and 2007.

Huge data sets might have to be done in batches if Word gets bogged down during pasting.


2014-02-23 06:42:46

Pawel

a super simple solution for Excel 2010 based on one of the previous comments

just copy/paste to Word and copy/paste the table from Word back to Excel


2013-06-17 13:52:13

SL Simmons

I find it easier to convert the formatting manually. Start with a copy of the sheet to be safe.
As an example... assume that cells in column A are formatted conditionally yellow when they match values in another column (vlookup formula as condition). The way I would "convert" the formtting to excplicit is as follows:
1) Make a note of the original order: Insert a column to the left (now column A) and fill down with values 1, 2, 3....etc..). Original data is now in column B
2) Sort columns A and B by color of column B. This will group all of the conditionally formatted yellow cells in column B.
3) Insert another to the right (column C) and you can now easily apply explicit yellow formatting to the cells in this column because the formatted cells in the adjacent column B are contiguous. Simply seelct the adjacent range and fill.
4) Now simply re-sort all three columns by column A by ascending values to restore the original order.
Hope this makes sense...it's much quicker to do than describe.


2013-06-17 13:28:17

SL Simmons

If formatted by color I found it easier to do this manually. Start with a copy of the sheet to be safe.
Assuming column A is conditionally formatted yellow when conditions are met....
1) Insert an adjacent column and note the original order in new Column A (1,2,3...). Column B now contains the original data. Then
2) Re-sort both columns A and B by color. Then
3) Insert another column C and you can easily fill the appropirate cells yellow because the formatted cells in B are now contiguous. Then restore the orginal order by sorting columns A, B, and C by column A Values ascending.
4) Now you can copy and paste the explicit format from column C to wherever you need.


2013-03-21 01:51:09

Thorstein

I found the following to work in the later versions of Excel:

' In Excel 2003 and before we can just evaluate FC.Formula1
' In 2007 and after the formula will be relative to the upper left corner of the
' area for which the conditonal format applies. Hence rejig a bit to make the
' formula relevant to current cell:
f3 = FC.Formula1
f3 = Application.ConvertFormula(Formula:=f3, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1, RelativeTo:=FC.AppliesTo.Cells(1, 1))
f3 = Application.ConvertFormula(Formula:=f3, FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlR1C1, ToAbsolute:=xlAbsolute, RelativeTo:=rng)
f3 = Application.ConvertFormula(Formula:=f3, FromReferenceStyle:=xlR1C1, ToReferenceStyle:=xlA1)
If Application.Evaluate(f3) Then

I also moved the ndx loop up into PasteFC() as we can have several FormatConditions that apply (note StopIfTrue). And the copy of border information didn't quite work for me - change to only copy if source is not null.


2013-03-07 15:06:02

Chris

Good module but it didn't work for me.

I tried the code in Excel 2007. It compiled, ran, and delivered the confirmation msgbox that the conditional formatting had been removed, but it was not.

Instead I tried CStroliaDavis' approach and saved as mht and that worked great.

Thanks!


2013-02-21 17:25:13

CStroliaDavis

An even easier way to do this, and I believe it has worked for me for quite a number of versions of Excel, but I'm not 100% certain how many, is to save/publish your worksheet as an .mht (web page complete) file.

This usually saves all the formatting to the mht, but not the conditions.

Once you have done this open the file back up in Excel. Once you've opened it back up in Excel, you can copy the worksheet back to your original workbook somewhere.


2013-02-08 09:36:49

Allen

Richard's solution worked for me in Excel 2007.

I did have to do some cleaning up of the borders and % decimal places, but otherwise, it was perfect. That was a lot faster than editing conditional formatting for a couple hundred rows.


Thanks, Richard!


2013-02-06 20:35:26

Mike McCarthy

I have installed the "Save as pdf or XPS" add in. I can print (save as) to
pdf from Word 2007 with the page size automatically at A4, but doing the same
thing in Excel 2007 produces a most peculiar page size. All my printers have A4 set as the default. Note that un-checking “Scale content for A4 or 8.5”x11” does not work.

The issue seems to be connected with the scaling of the page. If it is set at 100%, then it prints correctly. If it is set to print at, say, 50%, it will produce a pdf double A4 size (A3).

Has anyone any suggestions how to fix this?


2013-01-07 10:39:48

Sean

Following steps above and it does not work for me not sure whether its because i have multiple conditions on one cell?

I have copied and pasted the above code into a VB module and i get the issue of 'Subscript out of Range' when run!

when debugging VBA takes me to the line

Select Case FC.Type

Under the function ActiveCondition

Anyone know whats going on been at this for hours and not know whats happening - eyes and mind in meltdown!


2012-11-28 21:24:58

Mike

Unfortunately, what is not mentioned about Function ActiveCondition is the criteria for running it. This is noted in http://www.cpearson.com/excel/CFColors.htm

I'm using Formula Is" rather than
"Cell Value Is" condition, AND
The formula used in the condition formula contains relative addresses.

Can anyone suggest a way around this issue?


2012-10-24 12:11:58

Salim

Thank You.

Tried in Excel 2007 and works Perfect.

Now I am looking for a way to restore the formatting condition back to its original state.


2012-09-13 03:02:42

Imran

Thanks,

This came in very handy. I was going to have to do it manually for 8000 rows of data.


2012-02-28 14:24:39

Richard

As a workaround to making the conditional formating permanent in Excel 2010, I created a blank Word document with the following Macro (shortcut Ctrl-Shift-m):

Sub Macro1()
'
' Macro1 Macro
'
'
Selection.PasteExcelTable False, False, False
Selection.HomeKey Unit:=wdStory, Extend:=wdExtend
Selection.Cut
End Sub


With both your Excel spreadsheet and this (blank) Word document open, you:
1) Select the Excel cells you want to make "conditional fills permanent"
2) Ctrl-C (to copy)
3) Switch to the (blank) Word document
4) Ctrl-Shift-m to paste and cut
5) After your cells appear and then disappear, switch back to your spreadsheet (same area still selected)
6) Ctrl-V (to paste)


2011-11-22 16:01:07

Steve

Alan,

Do you have any sort of work around for Excel 2010?


Thanks,

Steve


2011-11-04 11:55:09

awyatt

Thanks for the explanation of why this doesn't work in 2010, Richard.

Just a reminder to everyone: This tip (as it says at the end) only works in versions up through Excel 2003.


2011-11-04 11:37:57

Richard

Prior to Excel 2007, conditional formating are "redefined" cell by cell when you copy formats from 1 cell to another. Each cell equations changed appropriately (e.g. If copied to the cell just below the original, the equation columns remained the same and rows increased by 1). In Excel 2010 (I never used 2007), the format equations for every cell are IDENTICAL, a range is defined for all the cells, and Excel internally does the "redefinitions". The above macro "falsely" sees identical cell references for every "cell" in the (Excel 2010) defined range so all "permanent" formating is made based on the original cell references. Even using RC cell references in the conditional formating equations doesn't yield the desired "redefinitons" for each cell.


2011-11-04 06:10:18

Alan

Tried it in Excel 2010 and it doesn't seem to work. Don't know if it works in any of the versions mentioned (2003 being the latest).


Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing
Share