# Excel 2007 conditional formatting formula problem

I have the following problem. I have a sheet where the current month should be highlighted. For this I used the formula for conditional formatting. The formula for the first three months is like this:

``````#M1
=IF(AND(TODAY()>=DATE(YEAR(\$B\$5);MONTH(\$B\$5);DAY(\$B\$5));TODAY()<=DATE(YEAR(\$B\$5);MONTH(\$B\$5)+1;    DAY(B5)));TRUE;FALSE)

#M2
=IF(AND(TODAY()>=DATE(YEAR(\$B\$5);MONTH(\$B\$5)+1;DAY(\$B\$5)+1);TODAY()<=DATE(YEAR(\$B\$5);    MONTH(\$B\$5)+2;DAY(\$B\$5)));TRUE;FALSE)

#M3
=IF(AND(TODAY()>=DATE(YEAR(\$B\$5);MONTH(\$B\$5)+2;DAY(\$B\$5)+1);TODAY()<=DATE(YEAR(\$B\$5);    MONTH(\$B\$5)+3;DAY(\$B\$5)));TRUE;FALSE)
``````

B5 is a cell which contains the start date. To write all these changing formulas into cells I used a macro.

My problem is that when I want to add an additional cell and manually change the formula, it doesn't work anymore. Suddenly the range is different and what-not. I have to write these formulas again with the macro as well as the additional month. It screws up every time I want to do it manually.

Does anyone know why this happens and how to fix it?

-
I'm having a hard time understanding the details of your problem. I think you need some more specific explanation of what's going on. – Lance Roberts Sep 2 '11 at 15:27
"Suddenly the range is different and what-not" & "changing formulas" -- I'm not sure what you mean by these. Could you care to elaborate? – Kaze Sep 2 '11 at 18:20