删除条件格式，但不删除效果（Microsoft Excel）
查理（Charlie）想知道是否有一种方法可以在任何给定时间使条件格式的效果永久化。例如，如果条件格式指定特定单元格为粗体红色类型，则Charlie想要一种删除条件格式并使单元格为粗体和红色的方法。
在Excel中没有执行此操作的内在方法。 “选择性粘贴”选项均不会根据需要执行此任务。但是，您可以使用宏来完成任务：
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
此解决方案中包含三个过程。最后一个过程ActiveCondition旨在返回一个数字，该数字指示当前处于条件格式的条件中的哪些条件有效。该例程在Chip Pearson的站点上找到，如该功能的第一条评论所述。 （重新发明轮子没有意义。：>））
中心函数NewFC仅用于确定两个值中的哪个有效。但是，您实际运行的过程是PasteFC。
只需选择要转换为显式格式的单元格，然后运行该过程。它检查所选的每个单元格的格式化条件处于活动状态，确定该条件的格式，然后将其应用于该单元格。最后，删除单元格的条件格式。
注意：
如果您想知道如何使用此页面（或_ExcelTips_网站上的任何其他页面）中描述的宏，我准备了一个特殊页面，其中包含有用的信息。
_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。
本技巧（1947）适用于Microsoft Excel 97、2000、2002和2003。