vba Excel：将条件格式设为静态

Excel: Make conditional formatting static

提问by Martin

Is there any way to convert conditional formatting to static formatting in Excel?

有没有办法在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.

我正在尝试将一系列 Excel 工作表导出到一个新的工作簿，具有相同的外观但没有公式、链接等。这里的问题是我的条件格式依赖于导出范围之外的计算。

I've tried saving the workbook to .html, oddly enough the formatting shows in IE but not when reopening it in Excel.

我试过将工作簿保存为 .html，奇怪的是格式显示在 IE 中，但在 Excel 中重新打开时却没有。

采纳答案by Dr. belisarius

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

以下想法取自here，尽管进行了修改以适应一些新的条件格式结构和您的需求。

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!

它的工作原理是这样的：给定一个带有一些条件格式的工作簿（复制你的），你在 Sub a() 中放入你想要从条件格式转换为直接格式的单元格范围，然后运行宏。之后，只需手动删除条件格式，然后就可以了！

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

回答by Chris Rae

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.

如果这不起作用，belisarius 的代码看起来很棒。

回答by zweettooth

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!

我把贝利撒留和卡梅隆·福特的补充放在一起。您必须选择要冻结的区域（大的选择可能需要一段时间）。我注意到如果单元格上有 excel 错误，它可能会导致异常，但否则这在 Excel 2010 上运行良好。顺便说一句，谢谢大家！

---

---

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

回答by gcl

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.

感谢贝利撒留非常有用的答案！但是，它遇到了 Excel 2003 中的一个错误，即在多个/扩展选择中的任何单元格上查询条件格式公式会返回该选择中第一个单元格的公式！为了解决这个问题，我不得不在开始时取消任何选择并在最后恢复它。我还将他的子例程更改为一个函数，该函数接受一个范围并返回修改后的单元格数量，并添加了一个包装子例程，将其应用于当前选择并删除任何条件格式（因为不再需要），因此您不再需要修改它以硬编码您的目标范围。

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

回答by Cameron Forward

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:

我在 excel.tips.com 上找到了这个附加内容，使这项工作适用于 Excel 2010 并将其改编为贝利撒留的 gcl 版本的帖子。在 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.

它使公式正确向下和交叉传播。

回答by pooroldpedro

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