CODEWITHSUNDEEP
vbaexcelconditional-formatting
robbiemck
robbiemck

Reputation: 13

Excel VBA: Editing existing conditional formatting rules for new rows

I've got a spreadsheet with some conditional formatting applied to certain ranges of cells (eg. $B$4:$F$5). I set up a macro to add a new row, copying the formatting + formulas in the current last row. Problem is, each time I add a new row an additional conditional formatting rule is created affecting only the new row.

How do I use VBA to extend the conditional I've already set up - making the rule affect $B$4:$F$6?

Upvotes: 0

Views: 975

Answers (1)

user4039065
user4039065

Reputation:

Fill down and delete the constants instead of copying and paste special, values and formats. That should unequivocally extend the CFR to the new row.

With Worksheets("sheet1")
    With .Range(.Cells(4, "B"), .Cells(.Rows.Count, "F").End(xlUp))
        .Offset(.Rows.Count - 1, 0).Resize(2, .Columns.Count).FillDown
        On Error Resume Next
        'Debug.Print .Offset(.Rows.Count, 0).Resize(1, .Columns.Count).Address(0, 0)
        .Offset(.Rows.Count, 0).Resize(1, .Columns.Count).SpecialCells(xlCellTypeConstants, 23).ClearContents
        On Error GoTo 0
    End With
End With

Upvotes: 1

Related Questions