Como posso modificar a propriedade “Aplica-se a” da regra de formatação condicional de uma célula usando VBA?

Question

Estou tentando usar o VBA para criar uma regra de formatação condicional e aplicá-la a várias células.

Eu tentei isso criando as regras para apenas uma célula e depois editando a propriedade applyto para cada objeto formatcondition. Veja aqui:

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

Isso parece não funcionar porque, uma vez alcançado a linha AppliesTo, ele realmente altera o valor da própria célula, em vez de modificar a propriedade AppliesTo da condição de formato.

Qual é a melhor maneira de modificar a propriedade “Aplica-se a” da regra de formatação condicional de uma célula?

Eu sei que posso modificar minha instrução “with” para incluir as outras células que desejo modificar; não apenas a célula B4. O problema com isso é que o objeto range() só pode aceitar um argumento de string que não tenha mais de 1.024 caracteres. E, na verdade, quero aplicar esta regra a um grande número de células não contíguas que ultrapasse o limite de 1.024 caracteres.

Answer 1

Existe um ModifyAppliesToRangemétodoprojetado para esse fim. Você também precisará converter strRangepara um objeto Range.

Substituir .FormatConditions(1).AppliesTo = strRange com .FormatConditions(1).ModifyAppliesToRange Range(strRange)

Answer 2

Tive um problema semelhante, combinado com a aplicação da Formatação Condicional a várias áreas após cortar e colar células. Por exemplo, (E4:G4, E6:G6, etc.).

Basicamente, tive que determinar a extensão dos intervalos e então usei ModifyAppliesToRange para atualizá-lo.

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