Inserting/Deleting Rows Causes Issues with Conditional Formatting Macro

I have a macro that does a bunch of conditional formatting {i.e. changes the fort color based on date (items older than today are red, today plus the next 7 days are purple) and shades other items mint green if column G has 1 or 3 or H has a 1 - this probably TMI)}. See below. There's also a sample attached.

The macro works fine. It does exactly what it should. Here's the problem. If I insert or deleted a row, the conditional formatting rule breaks/splits. The best way to describe it is.....the formatting is stuck in/with the cell despite the fact that the data (which is what caused the cell's font/fill to change in the 1st place, but that's the point of a conditional) has been shifted. Any advice on how to fix it?

Please Login or Register  to view this content.

I'm not seeing the problem. I ran the code to set the conditionals, then inserted and deleted rows, and the conditionals continue to work just fine on the new rows as well as the originals.

It appears that the problem occurs when you insert rows before row 1. Hopefully I can explain why, though I may not have the correct understanding myself, but I think it's close. When your code creates the conditions, it does so using relative row #1 ($G1, $I1, $H1). If you look at the conditional formatting in a cell in any row, it will still have 1 in the formula, which apparently points to itself (the cell's row) rather than actual row 1. If you insert rows after row 1, the conditions in those and subsequent rows still point to themselves. However, if you insert, say 3 rows before row 1, all the subsequent rows' conditional format formulas get bumped up by 3 rows, so they will then say $G4, $I4, and$H4. I don't totally understand it, but that's what is happening. You can address it by either not inserting rows before row 1, or by just rerunning your code after your insertion.

Just put this code in a subroutine called by the other macro where needed, and you can run it when needed.