excel - EntireRow.Delete is affecting my Conditional Formatting ranges -

i'm looking away delete rows without affecting conditional formatting ranges i've applied worksheet. want cf ranges stay same (ex. $a$1:$a$5000) whenever code deletes row, second number (5000) reduced one, when want stay same. there anyway can delete rows without affecting cf?

i know apply conditional formatting worksheet dynamic range each time run macro, i'd rather not go through hassle of rewriting ~20 cf scenarios vba macro if can avoid changing together. here loop steps through rows , deletes them if meets criteria:

activeworkbook.worksheets("master sheet").activate last = cells(rows.count, "r").end(xlup).row      = last 1 step -1         if (cells(i, "r").value) = "business"             cells(i, "a").entirerow.delete         end if     next 

i've thought alternative solution add simple loop cycle through cfs set , drag them down lastrow. viable alternative?

this quick loop should change terminating row of every cfr on worksheet 5000 assuming start in row 1.

with activeworkbook.worksheets("master sheet").cells     cfr = 1 .formatconditions.count         .formatconditions(cfr)             if .appliesto.cells(.appliesto.cells.count).row < 5000                 .modifyappliestorange range:=.appliesto.cells(1).resize(5000)             end if         end     next cfr end