将条件格式设置为静态
在Excel中是否可以将条件格式转换为静态格式?
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.
我尝试将工作簿保存为.html，奇怪的是IE中显示了格式，但在Excel中重新打开时却没有.
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.
它的工作原理是:给工作簿添加一些条件格式(制作副本)，然后将要从条件格式转换为纯格式的单元格范围放入Sub a()中，然后运行宏.之后，只需手动删除条件格式，然后保存即可！
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