Written by Allen Wyatt (last updated June 14, 2025)
This tip applies to Excel 97, 2000, 2002, and 2003
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.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (1947) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
If an error exists in a formula tucked inside a conditional format, you may never know it is there. There are ways to ...
Discover MoreConditional formatting is a great way to make sure that your information looks a particular way, even if the information ...
Discover MoreConditional formatting can be used to highlight cells that contain the improper type of data for your needs. This tip ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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 © 2025 Sharon Parq Associates, Inc.
Comments