¿Cómo puedo modificar la propiedad "Se aplica a" de la regla de formato condicional de una celda usando VBA?

Question

Estoy intentando usar VBA para crear una regla de formato condicional y aplicarla a varias celdas.

Lo intenté creando las reglas para una sola celda y luego editando la propiedad applyto para cada objeto de condición de formato. Mira aquí:

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

Esto no parece funcionar porque una vez que llega a la línea AppliesTo, en realidad cambia el valor de la celda en lugar de modificar la propiedad AppliesTo de la condición de formato.

¿Cuál es la mejor manera de modificar la propiedad "Se aplica a" de la regla de formato condicional de una celda?

Sé que puedo modificar mi declaración "con" para incluir las otras celdas que quiero modificar; no solo la celda B4. El problema con esto es que el objeto range() solo puede aceptar un argumento de cadena que no tenga más de 1024 caracteres. Y, en verdad, quiero aplicar esta regla a una gran cantidad de celdas no contiguas que superen ese límite de 1024 caracteres.

Answer 1

Hay un ModifyAppliesToRangemétododiseñado para este propósito. También necesitarás convertirlo strRangea un objeto Range.

Reemplazar .FormatConditions(1).AppliesTo = strRange con .FormatConditions(1).ModifyAppliesToRange Range(strRange)

Answer 2

Tuve un problema similar, combinado con el formato condicional aplicado a múltiples áreas después de cortar y pegar celdas. Por ejemplo, (E4:G4, E6:G6, etc.).

Básicamente, tuve que determinar la extensión de los rangos y luego usé ModifyAppliesToRange para actualizarlo.

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