EntireRow.Delete is affecting my Conditional Formatting ranges

Question

I'm looking for away to delete rows without it affecting my conditional formatting ranges that I've applied to my worksheet. I want my CF ranges to stay the same (ex. $A$1:$A$5000) but whenever my code deletes a row, the second number (5000) is reduced by one, when I want it to stay the same. Is there anyway I can delete my rows without it affecting the CF?

I also know that I could just apply conditional formatting to the worksheet with a dynamic range each time I run the macro, but I'd rather not go through the hassle of rewriting ~20 CF scenarios into VBA for this macro if I can just avoid it changing all together. Here is my loop which steps through the rows and deletes them if it meets the criteria:

ActiveWorkbook.Worksheets("Master Sheet").Activate
Last = Cells(Rows.Count, "R").End(xlUp).Row

    For i = Last To 1 Step -1
        If (Cells(i, "R").Value) = "Business" Then
            Cells(i, "A").EntireRow.Delete
        End If
    Next I

I've thought that an alternative solution would be to add a simple loop to cycle through all my CFs that are set up and drag them down to the lastrow. Would this be a viable alternative?

You can use INDEX as your references in the all your CF: $A$1:$A$5000 = INDEX($A:$A,1):INDEX($A:$A,5000) — Scott Craner
Maybe use this range instead to define your CF? Range("A:A") as that will apply to the entire column. — braX
@braX Sorry my example above was poor, I can't use the whole column of A because there are a couple header rows which force me to start at A4, and it won't let me choose $A$4:$A as an option — dwirony
You can set the entire column, and then after that remove the CF from the individual cells that dont need it. — braX
@ScottCraner Thank you for your help anyways. I ended up just setting them all to $A$2:$A$1048576 and it stays like that just fine. — dwirony

Unknown Unknown · Accepted Answer · 2017-07-28T18:25:46

This quick loop should change the terminating row of every CFR on the worksheet to 5000 assuming that they start in row 1.

With ActiveWorkbook.Worksheets("Master Sheet").Cells
    For cfr = 1 To .FormatConditions.Count
        With .FormatConditions(cfr)
            If .AppliesTo.Cells(.AppliesTo.Cells.Count).Row < 5000 Then
                .ModifyAppliesToRange Range:=.AppliesTo.Cells(1).Resize(5000)
            End If
        End With
    Next cfr
End With

EntireRow.Delete is affecting my Conditional Formatting ranges

1 Answers