这是我对这个问题的回答。我仅将其实现为使用公式的条件格式,因为我很少使用其他条件格式类型。它也可以作为我的个人网站的加载项提供:合并条件格式化 v1.2 https://rath.ca/Misc/VBA/Excel/MergeConditionalFormatting%20v1.2.zip
这是代码:
'''
' MergeConditionalFormatting - Add-in to merge conditional formatting.
' Author: Christopher Rath <[email protected] /cdn-cgi/l/email-protection>
' Date: 2020-12-17
' Version: 1.0
' Archived at: http://www.rath.ca/Misc/VBA/
' Copyright © 2020 Christopher Rath
' Distributed under the GNU Lesser General Public License v2.1
' Warranty: None, see the license.
'''
Option Explicit
Option Base 1
' See https://learn.microsoft.com/en-us/office/vba/api/excel.formatcondition
Public Sub MergeCF()
Dim cfBase As Object
Dim cfCmp As Object
Dim iBase, iCmp As Integer
Dim delCount As Integer
Application.ScreenUpdating = False
delCount = 0
With ActiveSheet.Cells
'Debug.Print "Base", "Applies To", "Type", "Formula", "|", "Match", "|", "Cmp", "Applies To", "Type", "Formula"
iBase = 1
Do While iBase <= .FormatConditions.Count
Set cfBase = .FormatConditions.Item(iBase)
Application.StatusBar = "Checking FormatCondition " & iBase
If (cfBase.Type = xlCellValue) Or (cfBase.Type = xlExpression) Then
For iCmp = .FormatConditions.Count To (iBase + 1) Step -1
Application.StatusBar = "Checking FormatCondition " & iBase & " to " & iCmp
Set cfCmp = .FormatConditions.Item(iCmp)
'Debug.Print iBase, cfBase.AppliesTo.Address(, , xlR1C1), cfBase.Type, _
' Application.ConvertFormula(cfBase.Formula1, xlA1, xlR1C1, , _
' cfBase.AppliesTo.Cells(1, 1)), _
' "|", IIf(cmpFormatConditions(cfBase, cfCmp), "True", "False"), "|", _
' iCmp, cfCmp.AppliesTo.Address(, , xlR1C1), cfCmp.Type, _
' Application.ConvertFormula(cfCmp.Formula1, xlA1, xlR1C1, , _
' cfCmp.AppliesTo.Cells(1, 1))
If (cfCmp.Type = xlCellValue) Or (cfCmp.Type = xlExpression) Then
If cmpFormatConditions(cfBase, cfCmp) Then
cfBase.ModifyAppliesToRange Union(cfCmp.AppliesTo, cfBase.AppliesTo, cfCmp.AppliesTo)
cfCmp.Delete
delCount = delCount + 1
' Testing has shown that the .Delete of the extra FormatCondition has caused the
' FormatConditions collection to become changed; e.g., item(1) is no longer
' guaranteed to be the same FormatCondition object that it was prior to the
' .Delete. So, we will now re-jig the value if iBase so that it restarts at
' item(1) and once once again starts its scan from scratch.
iBase = 1
GoTo RESTART
End If
End If
Next iCmp
End If
iBase = iBase + 1
RESTART:
Loop
End With
Application.ScreenUpdating = True
Application.StatusBar = "Consolidated " & delCount & " FormatCondition records."
End Sub
Private Function cmpFormatConditions(ByRef cfBase As FormatCondition, ByRef cfCmp As FormatCondition, _
Optional ByVal comparePriority As Boolean = False) As Boolean
Dim rtnVal As Boolean
' We set the return value (rtnVal) to false, and then test each property.
' If any individual test evaluates to false then we fall to the bottom of the if-thens
' and return the initial value (false). If we make it through all the tests, then we
' change rtnVal to true before returning.
'
' We test each property in reverse alphabetic order because most of the simple types are then tested
' first; which should speed up the code.
'
' NOTE: The Priority property cannot be compared because this is simply the number that reflects
' the order in which the FormatCondition records are evaluated. That said, we do allow this
' to behaviour to be overridden through an optional parameter.
'
rtnVal = False
If cfBase.Type = cfCmp.Type Then
' The specific properties to test is dependent upon the Type.
Select Case cfBase.Type
Case xlCellValue, xlExpression
If cfBase.StopIfTrue = cfCmp.StopIfTrue Then
If cfBase.PTCondition = cfCmp.PTCondition Then
If (Not comparePriority) Or (comparePriority And cfBase.Priority = cfCmp.Priority) Then
If cmpNumberFormat(cfBase.NumberFormat, cfCmp.NumberFormat) Then
If cmpInterior(cfBase.Interior, cfCmp.Interior) Then
If Application.ConvertFormula(cfBase.Formula1, xlA1, xlR1C1, , cfBase.AppliesTo.Cells(1, 1)) _
= Application.ConvertFormula(cfCmp.Formula1, xlA1, xlR1C1, , cfCmp.AppliesTo.Cells(1, 1)) Then
If cmpFont(cfBase.Font, cfCmp.Font) Then
If cmpBorders(cfBase.Borders, cfCmp.Borders) Then
rtnVal = True
End If
End If
End If
End If
End If
End If
End If
End If
Case Else
' Ultimately we need to throw a hard error.
rtnVal = False
End Select
End If
cmpFormatConditions = rtnVal
End Function
Private Function cmpBackground(ByRef bBase As Variant, ByRef bCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(bBase) And IsNull(bCmp) Then
rtnVal = True
ElseIf Not IsNull(bBase) And Not IsNull(bCmp) Then
If bBase = bCmp Then
rtnVal = True
End If
End If
cmpBackground = rtnVal
End Function
Private Function cmpBold(ByRef bBase As Variant, ByRef bCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(bBase) And IsNull(bCmp) Then
rtnVal = True
ElseIf Not IsNull(bBase) And Not IsNull(bCmp) Then
If bBase = bCmp Then
rtnVal = True
End If
End If
cmpBold = rtnVal
End Function
Private Function cmpBorder(ByRef bBase As Border, ByRef bCmp As Border) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If bBase.Color = bCmp.Color Then
If bBase.ColorIndex = bCmp.ColorIndex Then
If Not IsObject(bBase.ThemeColor) And Not IsObject(bCmp.ThemeColor) Then
rtnVal = True
ElseIf (Not IsObject(bBase.ThemeColor)) And (Not IsObject(bCmp.ThemeColor)) Then
If bBase.ThemeColor = bCmp.ThemeColor Then
If bBase.Weight = bCmp.Weight Then
If bBase.LineStyle = bCmp.LineStyle Then
If bBase.TintAndShade = bCmp.TintAndShade Then
rtnVal = True
End If
End If
End If
End If
End If
End If
End If
cmpBorder = rtnVal
End Function
Private Function cmpBorders(ByRef bBase As Borders, ByRef bCmp As Borders) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If cmpBorder(bBase(xlDiagonalDown), bCmp(xlDiagonalDown)) Then
If cmpBorder(bBase(xlDiagonalUp), bCmp(xlDiagonalUp)) Then
If cmpBorder(bBase(xlEdgeBottom), bCmp(xlEdgeBottom)) Then
If cmpBorder(bBase(xlEdgeLeft), bCmp(xlEdgeLeft)) Then
If cmpBorder(bBase(xlEdgeRight), bCmp(xlEdgeRight)) Then
If cmpBorder(bBase(xlEdgeTop), bCmp(xlEdgeTop)) Then
If cmpBorder(bBase(xlInsideHorizontal), bCmp(xlInsideHorizontal)) Then
If cmpBorder(bBase(xlInsideVertical), bCmp(xlInsideVertical)) Then
rtnVal = True
End If
End If
End If
End If
End If
End If
End If
End If
cmpBorders = rtnVal
End Function
Private Function cmpColor(ByRef cBase As Variant, ByRef cCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(cBase) And IsNull(cCmp) Then
rtnVal = True
ElseIf Not IsNull(cBase) And Not IsNull(cCmp) Then
If cBase = cCmp Then
rtnVal = True
End If
End If
cmpColor = rtnVal
End Function
Private Function cmpColorIndex(ByRef cBase As Variant, ByRef cCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(cBase) And IsNull(cCmp) Then
rtnVal = True
ElseIf Not IsNull(cBase) And Not IsNull(cCmp) Then
If cBase = cCmp Then
rtnVal = True
End If
End If
cmpColorIndex = rtnVal
End Function
Private Function cmpFont(ByRef fBase As Font, ByRef fCmp As Font) As Boolean
Dim rtnVal As Boolean
rtnVal = False
' Is a Font object and so I need to build out tests for its properties.
If cmpBackground(fBase.Background, fCmp.Background) Then
If cmpBold(fBase.Bold, fCmp.Bold) Then
If cmpColor(fBase.Color, fCmp.Color) Then
If cmpColorIndex(fBase.ColorIndex, fCmp.ColorIndex) Then
If cmpFontStyle(fBase.FontStyle, fCmp.FontStyle) Then
If cmpItalic(fBase.Italic, fCmp.Italic) Then
If cmpName(fBase.Name, fCmp.Name) Then
If cmpSize(fBase.Size, fCmp.Size) Then
If cmpStrikethrough(fBase.Size, fCmp.Size) Then
If cmpSubscript(fBase.Size, fCmp.Size) Then
If cmpSuperscript(fBase.Size, fCmp.Size) Then
If cmpThemeColor_V(fBase, fCmp) Then
If fBase.ThemeFont = fCmp.ThemeFont Then
If cmpTintAndShade(fBase.TintAndShade, fCmp.TintAndShade) Then
If cmpUnderline(fBase.Underline, fCmp.Underline) Then
rtnVal = True
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
cmpFont = rtnVal
End Function
Private Function cmpFontStyle(ByRef fBase As Variant, ByRef fCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(fBase) And IsNull(fCmp) Then
rtnVal = True
ElseIf Not IsNull(fBase) And Not IsNull(fCmp) Then
If fBase = fCmp Then
rtnVal = True
End If
End If
cmpFontStyle = rtnVal
End Function
Private Function cmpGradient(ByRef gBase As Variant, ByRef gCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If (gBase Is Nothing) And (gCmp Is Nothing) Then
rtnVal = True
ElseIf Not (gBase Is Nothing) And Not (gCmp Is Nothing) Then
If gBase = gCmp Then
rtnVal = True
End If
End If
cmpGradient = rtnVal
End Function
Private Function cmpInterior(ByRef iBase As Interior, ByRef iCmp As Interior) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If iBase.Color = iCmp.Color Then
If cmpColorIndex(iBase.ColorIndex, iCmp.ColorIndex) Then
If cmpGradient(iBase.Gradient, iCmp.Gradient) Then
If cmpPattern(iBase.Pattern, iCmp.Pattern) Then
If cmpPatternColor(iBase.PatternColor, iCmp.PatternColor) Then
If cmpPatternColorIndex(iBase.PatternColorIndex, iCmp.PatternColorIndex) Then
If cmpPatternThemeColor(iBase.PatternThemeColor, iCmp.PatternThemeColor) Then
If cmpPatternTintAndShade(iBase.PatternTintAndShade, iCmp.PatternTintAndShade) Then
If cmpThemeColor_V(iBase, iCmp) Then
If cmpTintAndShade(iBase.TintAndShade, iCmp.TintAndShade) Then
rtnVal = True
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
cmpInterior = rtnVal
End Function
Private Function cmpItalic(ByRef iBase As Variant, ByRef iCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(iBase) And IsNull(iCmp) Then
rtnVal = True
ElseIf Not IsNull(iBase) And Not IsNull(iCmp) Then
If iBase = iCmp Then
rtnVal = True
End If
End If
cmpItalic = rtnVal
End Function
Private Function cmpName(ByRef nBase As Variant, ByRef nCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(nBase) And IsNull(nCmp) Then
rtnVal = True
ElseIf Not IsNull(nBase) And Not IsNull(nCmp) Then
If nBase = nCmp Then
rtnVal = True
End If
End If
cmpName = rtnVal
End Function
Private Function cmpNumberFormat(ByRef nfBase As Variant, ByRef nfCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsEmpty(nfBase) And IsEmpty(nfCmp) Then
rtnVal = True
ElseIf (Not IsEmpty(nfBase)) And (Not IsEmpty(nfCmp)) Then
If nfBase = nfCmp Then
rtnVal = True
End If
End If
cmpNumberFormat = rtnVal
End Function
Private Function cmpPattern(ByRef pBase As Variant, ByRef pCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(pBase) And IsNull(pCmp) Then
rtnVal = True
ElseIf Not IsNull(pBase) And Not IsNull(pCmp) Then
If pBase = pCmp Then
rtnVal = True
End If
End If
cmpPattern = rtnVal
End Function
Private Function cmpPatternColor(ByRef pBase As Variant, ByRef pCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(pBase) And IsNull(pCmp) Then
rtnVal = True
ElseIf Not IsNull(pBase) And Not IsNull(pCmp) Then
If pBase = pCmp Then
rtnVal = True
End If
End If
cmpPatternColor = rtnVal
End Function
Private Function cmpPatternColorIndex(ByRef pBase As Variant, ByRef pCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(pBase) And IsNull(pCmp) Then
rtnVal = True
ElseIf Not IsNull(pBase) And Not IsNull(pCmp) Then
If pBase = pCmp Then
rtnVal = True
End If
End If
cmpPatternColorIndex = rtnVal
End Function
Private Function cmpPatternThemeColor(ByRef pBase As Variant, ByRef pCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(pBase) And IsNull(pCmp) Then
rtnVal = True
ElseIf Not IsNull(pBase) And Not IsNull(pCmp) Then
If pBase = pCmp Then
rtnVal = True
End If
End If
cmpPatternThemeColor = rtnVal
End Function
Private Function cmpPatternTintAndShade(ByRef pBase As Variant, ByRef pCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(pBase) And IsNull(pCmp) Then
rtnVal = True
ElseIf Not IsNull(pBase) And Not IsNull(pCmp) Then
If pBase = pCmp Then
rtnVal = True
End If
End If
cmpPatternTintAndShade = rtnVal
End Function
Private Function cmpSize(ByRef sBase As Variant, ByRef sCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(sBase) And IsNull(sCmp) Then
rtnVal = True
ElseIf Not IsNull(sBase) And Not IsNull(sCmp) Then
If sBase = sCmp Then
rtnVal = True
End If
End If
cmpSize = rtnVal
End Function
Private Function cmpStrikethrough(ByRef sBase As Variant, ByRef sCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(sBase) And IsNull(sCmp) Then
rtnVal = True
ElseIf Not IsNull(sBase) And Not IsNull(sCmp) Then
If sBase = sCmp Then
rtnVal = True
End If
End If
cmpStrikethrough = rtnVal
End Function
Private Function cmpSubscript(ByRef sBase As Variant, ByRef sCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(sBase) And IsNull(sCmp) Then
rtnVal = True
ElseIf Not IsNull(sBase) And Not IsNull(sCmp) Then
If sBase = sCmp Then
rtnVal = True
End If
End If
cmpSubscript = rtnVal
End Function
Private Function cmpSuperscript(ByRef sBase As Variant, ByRef sCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(sBase) And IsNull(sCmp) Then
rtnVal = True
ElseIf Not IsNull(sBase) And Not IsNull(sCmp) Then
If sBase = sCmp Then
rtnVal = True
End If
End If
cmpSuperscript = rtnVal
End Function
Private Function cmpThemeColor_V(ByRef vBase As Variant, ByRef vCmp As Variant) As Boolean
Dim rtnVal As Boolean
Dim baseErr, cmpErr As Boolean
baseErr = False
cmpErr = False
rtnVal = False
On Error GoTo ERR_BASE
' Force an evaluation of fcBase.ThemeColor. We only care if it was possible to read the property
' without generating an error.
If IsNull(vBase.ThemeColor) Then
' Empty clause.
End If
On Error GoTo ERR_CMP
' Force an evaluation of fcBase.ThemeColor. We only care if it was possible to read the property
' without generating an error.
If IsNull(vCmp.ThemeColor) Then
' Empty clause.
End If
On Error GoTo 0
If baseErr And cmpErr Then
rtnVal = True
ElseIf (Not baseErr) And (Not cmpErr) Then
If IsNull(vBase.ThemeColor) And IsNull(vCmp.ThemeColor) Then
rtnVal = True
ElseIf Not IsNull(vBase.ThemeColor) And Not IsNull(vCmp.ThemeColor) Then
If vBase.ThemeColor = vCmp.ThemeColor Then
rtnVal = True
End If
End If
End If
cmpThemeColor_V = rtnVal
Exit Function
ERR_BASE:
On Error Resume Next
baseErr = True
Resume
ERR_CMP:
On Error Resume Next
cmpErr = True
Resume
End Function
Private Function cmpTintAndShade(ByRef tbase As Variant, ByRef tcmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(tbase) And IsNull(tcmp) Then
rtnVal = True
ElseIf Not IsNull(tbase) And Not IsNull(tcmp) Then
If tbase = tcmp Then
rtnVal = True
End If
End If
cmpTintAndShade = rtnVal
End Function
Private Function cmpUnderline(ByRef uBase As Variant, ByRef uCmp As Variant) As Boolean
Dim rtnVal As Boolean
rtnVal = False
If IsNull(uBase) And IsNull(uCmp) Then
rtnVal = True
ElseIf Not IsNull(uBase) And Not IsNull(uCmp) Then
If uBase = uCmp Then
rtnVal = True
End If
End If
cmpUnderline = rtnVal
End Function