Excel：使条件格式化为静态 [英] Excel: Make conditional formatting static

问题描述

我正在尝试将Excel Sheet的范围导出到新的工作簿，其中包含相同的外观，但没有公式，链接等。这里的问题是我有条件格式，依赖于导出范围之外的计算。

我尝试将工作簿保存到.html，奇怪的是IE中的格式显示，但在Excel中重新打开时不是。

解决方案

从从 >，虽然修改为适合一些新的条件格式化结构和您的需要。

它的工作原理如下：给定一个有条件格式化的工作簿（制作你的副本），你将Sub a（）放在你要转换的单元格范围内从条件到直接格式化，并运行宏。之后，只需手动删除条件格式，并且presto！

对于代码长度很抱歉...生活有时像这样:(

  Option Explicit 
 Sub a（）
 
 Dim iconditionno As Integer 
 Dim rng，rgeCell As Range 
 Set rng = Range（A1：A10 ）
 
对于每个rgeCell在rng 
 
如果rgeCell.FormatConditions.Count<> 0然后
 iconditionno = ConditionNo（rgeCell）
如果iconditionno <> 0然后
 rgeCell.Interior.ColorIndex = rgeCell.FormatConditions（iconditionno）.Interior.ColorIndex 
 rgeCell.Font.ColorIndex = rgeCell.FormatConditions（iconditionno）.Font.ColorIndex 
结束如果
结束如果
下一个rgeCell 
 
 End Sub 
私有函数ConditionNo（ByVal rgeCell As Range）As Integer 
 
 Dim iconditionscount As Integer 
 Dim objFormatCondition As FormatCondition 
 
对于iconditionscount = 1到rgeCell.FormatConditions.Count 
设置objFormatCondition = rgeCell.FormatConditions （iconditionscount）
选择案例objFormatCondition.Type 
案例xlCellValue 
选择案例objFormatCondition.Operator 
案例xlBetween：如果Compare（rgeCell.Value，> =，objFormatCondition.Formula1 ）= True和_ 
比较（rgeCell.Value，<=，objFormatCondition.Formula2）= True Then _ 
 ConditionNo = iconditionscount 
 
 Case xlNotBetween：If Compare比较（rgeCell.Value，> =，objFormatCondition.Formula2）= True然后_ 
 ConditionNo = iconditionscount $（RgeCell.Value，< =，objFormatCondition.Formula1）= True And _ 
 b 
 $ b案例xlGreater：如果Compare（rgeCell.Value，>，objFormatCondition.Formula1）= True Then _ 
 ConditionNo = icondit ioncount 
 
 Case xlEqual：If Compare（rgeCell.Value，=，objFormatCondition.Formula1）= True Then _ 
 ConditionNo = iconditionscount 
 
 Case xlGreaterEqual：If比较（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 
 
案例xlNotEqual：If Compare（rgeCell.Value，<），objFormatCondition.Formula1）= True Then _ 
 Condi不是= iconditionscount 
 
如果ConditionNo> 0然后退出函数
结束选择
 
案例xlExpression 
如果Application.Evaluate（objFormatCondition.Formula1）然后
 ConditionNo = iconditionscount 
退出函数
结束如果
结束选择
 
下一个iconditionscount 
结束函数
 
私有函数比较（ByVal vValue1 As Variant，_ 
 ByVal sOperator As String，_ 
 ByVal vValue2 As Variant）As Boolean 
 
如果为左（CStr（vValue1），1）==则vValue1 = Application.Evaluate（vValue1）
如果左（CStr（vValue2），1）==然后vValue2 = Application.Evaluate（vValue2）
 
如果IsNumeric（vValue1）= True则vValue1 = CDbl（vValue1）
如果IsNumeric （vValue2）= True然后vValue2 = CDbl（vValue2）
 
选择案例sOperator 
案例=：比较=（vValue1 = vValue2）
案例<：比较=（vValue1< vValue2） 
 Case< =：Compare =（vValue1 <= vValue2）
 Case>：Compare =（vValue1> vValue2）
案例> =：Compare =（vValue1> = vValue2）
案例<>：Compare =（vValue1&vPalue2）
 End Select 
结束功能
  

Is there any way to convert conditional formatting to static formatting in 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.

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

解决方案

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

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!

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

这篇关于Excel：使条件格式化为静态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！