CODEWITHSUNDEEP
vbaexcelexcel-2013conditional-formatting
whytheq
whytheq

Reputation: 35577

How to update/change last row for all conditional rules' ranges

Here are our conditional formatting rules:

enter image description here

The actual scenario is much more complex although the first row of each AppliesTo range is always 5.

Once new data has been imported the last row of each AppliesTo range could change to say 15.

How can we apply this change using vba ?

This is our current loop that finds each AppliesTo range:

Sub updateAllCondFormatRules()

    Dim x As Excel.FormatCondition
    Dim r As Range

    Dim lastRw  As Integer
    Dim newLastRw  As Integer
    newLastRw = 15

    For Each x In ActiveSheet.Cells.FormatConditions
        Set r = x.AppliesTo
        MsgBox r.Address

        '>>here we need to change r so that the new last row is equal to newLastRw

        x.ModifyAppliesToRange r
    Next x

End Sub

Upvotes: 2

Views: 420

Answers (1)

R3uK
R3uK

Reputation: 14537

I had an issue with the type of x object, but this code seems to work :

Sub updateAllCondFormatRules()

    Dim x As Excel.FormatCondition
    Dim r As Range
    Dim lastRw  As Long
    Dim newLastRw  As Long

    newLastRw = 20

    For Each x In ActiveSheet.Cells.FormatConditions
        Set r = x.AppliesTo

        MsgBox r.Address
        '>>here we need to change r so that the new last row is equal to newLastRw
        'Set r = ActiveSheet.Range(Replace(r.Address, Split(r.Address, "$")(UBound(Split(r.Address, "$"))), newLastRw))
        Set r = r.Resize(newLastRw - r.Cells(1, 1).Row + 1, r.Columns.Count)
        MsgBox r.Address

        x.ModifyAppliesToRange r
    Next x

End Sub

Upvotes: 3

Related Questions