☰

Как изменить свойство «Применяется к» правила условного форматирования ячейки с помощью VBA?

tag-icon • tag-icon microsoft-excel vba microsoft-excel-2016 conditional-formatting
Как изменить свойство «Применяется к» правила условного форматирования ячейки с помощью VBA?

Я пытаюсь использовать VBA, чтобы создать правило условного форматирования и применить его к нескольким ячейкам.

Я попытался сделать это, создав правила только для одной ячейки, а затем отредактировав свойство applyto для каждого объекта formatcondition. Смотрите здесь:

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 символа.

решение1

Есть ModifyAppliesToRangeметодпредназначен для этой цели. Вам также потребуется преобразовать strRangeобъект в Range.

Заменить .FormatConditions(1).AppliesTo = strRange ​ .FormatConditions(1).ModifyAppliesToRange Range(strRange)

решение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

Связанный контент