Wie kann ich die Eigenschaft „Gilt für“ der Regel zur bedingten Formatierung einer Zelle mithilfe von VBA ändern?

Question

Ich versuche, mit VBA eine Regel zur bedingten Formatierung zu erstellen und sie auf mehrere Zellen anzuwenden.

Ich habe dies versucht, indem ich die Regeln nur für eine Zelle erstellt und dann die „appliesto“-Eigenschaft für jedes Formatcondition-Objekt bearbeitet habe. Siehe hier:

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

Dies scheint nicht zu funktionieren, da es, sobald es die Zeile „AppliesTo“ erreicht, tatsächlich den Wert der Zelle selbst ändert, anstatt die Eigenschaft „AppliesTo“ der Formatbedingung zu ändern.

Wie kann ich die Eigenschaft „Gilt für“ der bedingten Formatierungsregel einer Zelle am besten ändern?

Ich weiß, dass ich meine „with“-Anweisung ändern kann, um die anderen Zellen einzuschließen, die ich ändern möchte; nicht nur Zelle B4. Das Problem dabei ist, dass das range()-Objekt nur ein Zeichenfolgenargument akzeptieren kann, das nicht länger als 1024 Zeichen ist. Und in Wahrheit möchte ich diese Regel auf eine große Anzahl nicht zusammenhängender Zellen anwenden, die diese 1024-Zeichen-Begrenzung überschreiten.

Answer 1

Es gibt eine ModifyAppliesToRangeMethodefür diesen Zweck konzipiert. Sie müssen auch strRangein ein Range-Objekt konvertieren.

Ersetzen .FormatConditions(1).AppliesTo = strRange mit .FormatConditions(1).ModifyAppliesToRange Range(strRange)

Answer 2

Ich hatte ein ähnliches Problem, kombiniert mit der Anwendung der bedingten Formatierung auf mehrere Bereiche nach dem Ausschneiden und Einfügen von Zellen. Z. B. (E4:G4, E6:G6 usw.).

Im Grunde musste ich den Umfang der Bereiche bestimmen und ihn dann mit ModifyAppliesToRange aktualisieren.

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