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.

**Solve Real Business Problems** Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out *Microsoft Excel 2013 Data Analysis and Business Modeling* today!

Need to have a sound played if a certain condition is met? It is rather easy to do if you use a user-defined function to ...

Discover MoreTired of the default colors that Excel uses to display the row and column coordinates? You can modify the colors, but ...

Discover MoreThere are many times when you are creating a worksheet that you need to analyze dates within that worksheet. Once such ...

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

2018-01-29 15:13:27

Mike

2017-10-12 12:30:54

Michael (Micky) Avidan

Select the range you need to delete the CF Rules > Copy > move to a an empty Word document > Paste.

Select the pasted range > Copy > go back to your sheet and paste over the original range.

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” Excel MVP – Excel (2009-2018)

ISRAEL

2017-10-12 03:40:24

Michael (Micky) Avidan

Select the range you need to delete the CF Rules > Copy > move to a an empty Word document > Paste.

Select the pasted range > Copy > go back to your sheet and paste over the original range.

--------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” Excel MVP – Excel (2009-2018)

ISRAEL

2017-10-11 18:58:09

ryan

doesn't work

2016-09-04 06:11:56

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

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

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

2016-01-08 13:06:17

PGandhi

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

Thanks

2014-04-13 02:56:19

Guido Massaro

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

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

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

2013-06-17 13:52:13

SL Simmons

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

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

' 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

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

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

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

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

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

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

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

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

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

2011-11-04 06:10:18

Alan

Got a version of Excel that uses the
menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)?
**This site is for you!** If you
use a later version of Excel, visit
our *ExcelTips* site focusing on the ribbon interface.

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

Copyright © 2018 Sharon Parq Associates, Inc.

## Comments