How can I 'freeze' conditional formatting

[WaterGypsy]

Hi

I have a sheet that uses about 10 different conditional formatting rules across a number of columns. My problem is that whenever I add new rows (and I always do that by copying and pasting an existing row rather than inserting a new one) Excel seems to break up the formatting into different sections, sometimes it will cover a range of rows, other times just a single row. The formatting always seems to continue working but if I want to amend the formatting I end up having to re-establish the original formatting to the complete columns and delete all the superfluous rules generated by excel.

So my question is: how can I stop Excel from messing with the rules

thanks

 

[Special-K99]

I used VBA to delete existing CF rules and apply new ones. This usually results in one CF rule covering one range.