如何使用 VBA 修改儲存格條件格式規則的「套用於」屬性？

Question

我正在嘗試使用 VBA 建立條件格式規則並將其應用於多個單元格。

我嘗試透過僅為一個單元格建立規則，然後編輯每個 formatcondition 物件的 applyto 屬性來實現這一點。看這裡：

Sub test()

    Dim strRange As String
    Dim myRange As Range

    strRange = "$B$4,$B$9:$BS$9"

    With Sheets("Sheet1").Range("B4") 
        .FormatConditions.Delete
        .FormatConditions.Add xlExpression, xlEqual, "=ISBLANK(RC)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        .FormatConditions(1).StopIfTrue = True
        .FormatConditions(1).AppliesTo = strRange
    End With


    End Sub

這似乎不起作用，因為一旦到達AppliesTo行，它實際上會更改單元格本身的值，而不是修改格式條件的AppliesTo屬性。

修改單元格條件格式規則的「應用於」屬性的最佳方法是什麼？

我知道我可以修改我的“with”語句以包含我想要修改的其他單元格；不僅僅是 B4 單元格。這樣做的問題是 range() 物件只能接受不超過 1024 個字元的字串參數。事實上，我想將此規則應用於大量不連續的單元格，從而打破 1024 個字元的限制。

Answer 1

有一個ModifyAppliesToRange方法為此目的而設計。您還需要轉換strRange為 Range 物件。

用 .FormatConditions(1).AppliesTo = strRange 。 .FormatConditions(1).ModifyAppliesToRange Range(strRange)

Answer 2

我遇到了類似的問題，並在剪下和貼上單元格後將條件格式應用於多個區域。例如，（E4：G4、E6：G6 等）。

基本上，我必須確定範圍的範圍，然後使用ModifyAppliesToRange 來更新它。

Sub FixCondFormatDupRules()
'
Dim ws As Worksheet
Dim MyList As ListObject
Dim lRows As Long
Dim rngData As Range
Dim rngRow1 As Range
Dim rngRow2 As Range
Dim rngRowLast As Range

Set ws = ActiveSheet
Set MyList = ws.ListObjects(1)  'Note this only captures the first table in the ActiveSheet.  Wouldn't work if >1 table.
Set rngData = MyList.DataBodyRange
lRows = rngData.Rows.Count
Set rngRow1 = rngData.Rows(1)
Set rngRow2 = rngData.Rows(2)
Set rngRowLast = rngData.Rows(lRows)

With ws.Range(rngRow2, rngRowLast)
    .FormatConditions.Delete
End With

' Expanding the Conditional Formatting AppliesTo range to the extent of the ranges and to include the entire table column.
For Each col In rngRow1.Columns
    For Each fc In Range(col.Address).FormatConditions
        Set FirstCell = col                              'Find upper-left cell (lowest row, lowest col)
        Set LastCell = Cells(rngRowLast.Row, col.Column) 'Find lower-right cell (highest row, highest col)
        For Each xCell In fc.AppliesTo.Cells
            If xCell.Column < FirstCell.Column Then Set FirstCell = Cells(FirstCell.Row, xCell.Column)
            If xCell.Column > LastCell.Column Then Set LastCell = Cells(LastCell.Row, xCell.Column)
            If xCell.Row < FirstCell.Row Then Set FirstCell = Cells(xCell.Row, FirstCell.Column)
            If xCell.Row > LastCell.Row Then Set LastCell = Cells(xCell.Row, LastCell.Column)
        Next xCell
        fc.ModifyAppliesToRange Range(FirstCell, LastCell)
    Next fc
Next col

rngRow1.Cells(1, 1).Select
Application.CutCopyMode = False
End Sub