VBA + Conditional Formatting

[PhilBanks]

Hi,

Newbie here...please be gentle

Quick outline:
I'm struggling to add conditional formatting rules through VBA...in particular when the conditional formatting is done through a formula.
Would love to have some guidance, or point me in the direction of solutions (I've tried, but struggled)

More info:
I have a spreadsheet in excel that is working pretty much as I'm needing...except one thing...I can't workout how to add conditional formatting.
I can get conditional formatting to work on the sheet as wanted, but when I insert or delete a row through VBA, the conditional formatting doesn't copy/extend...so, what I'm after is once I add/delete a row with the buttons i've made, the script will automatically update the conditional formatting rules.
Here are the formulas I'm using for the conditional formatting:

Warning Format
[$H$21:$H$(Last Row-1 (LR))] =AND($E21="Message (additional)",$H21<>"")
[$I$21:$I$(LR)] =AND($E21<>"Intervals",$I21<>"")
[$G$21:$G$(LR)] =AND($E21<>"Intervals",$G21<>"")
[$F$21:$F$(LR)] =AND($E21<>"Free Ride",$F21<>"")
[$L$21:$L$(LR)] =AND($E21<>"Message (additional)",$L21<>0)
[$L$22:$L$(LR)] =AND($E22="Message (additional)",$L22=0)
[$L$22:$L$(LR)] =AND($E22="Message (additional)",($L22/86400)>=$AC22)
[$L$22:$L$(LR)] =AND($E22="Message (additional)",$E21="Message (additional)",$L22<=$L21)
[$K$21:$K$(LR)] Duplicate Values

Blocker Format
[$P$21:$Q$(LR),$U$21:$U$(LR),$W$21:$W$(LR),$Y$21:$Y$(LR),$AA$21:$AA$(LR)] =OR($E21="Free Ride",$E21="Constant")
[$G$21:$G$(LR),$I$21:$I$(LR),$S$21:$S$(LR)] =$E21<>"Intervals"
[$H$21:$H$(LR),$N$21:$R$(LR),$T$21:$AA$(LR)] =$E21="Message (additional)"
[$F$21:$F$(LR)] =$E21<>"Free Ride"

And (I think...as I'm really not sure what I'm doing) this is how I want the the different things formatted

Warning
(Basically red fill with white text)
With XXXXRANGEXXXX.FormatConditions(1).Font
        .Color = 16777215
    End With
    With XXXX.FormatConditions(1).Interior
        .Color = 192
End With

Blocker:
(Basically, dark grey fill, black diagnal pattern, black text)
With XXXXRANGEXXXX
        .FormatConditions(1).Font
		.Color = 0
	.FormatConditions(1).Interior
		.Color =  15204351
		.PatternColorIndex = -4105
		.TintAndShade = 0
		.Pattern = 1
End With

Alternate Row change:
Haven't looked at this yet

I've attached the workbook (it's the first sheet) so you can see what I'm trying to do. Any help would be hugely appreciated (and I've no doubt that there are better ways of doing what I've done formula wise...but it works for what I need )

Any advice would be hugely appreciated

Thanks,
Phil

[dflak]

Just a suggestion. Convert the range to an Excel Table. One of the features of an Excel table is that as rows are inserted / appended / deleted all the attributes for the column are copied down. This includes formulas, formatting (including conditional formatting) and data validations.

[PhilBanks]

Thanks for replying
I tried this, but it meant my insert/delete row buttons no longer worked...so if anyone knows how to get the buttons working on excel table, that could solve it maybe