Excel 2007 conditional formatting formula problem

Question

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?

Answer 1

One thing that would simplify your formula considerably and make it easier for you (and others) to troubleshoot is to simply drop the surrounding "IF". Just use the AND and this will naturally return True or False.

When editing formulas in the conditional formatting dialog, press F2 to go into edit mode (same as for a cell) then you can use arrow keys etc to move through the formula without it inserting extra cell ranges based on where the active cell is. This might be what you mean by "Suddenly the range is different and what-not" but I am not sure.

As it stands, your formula does not highlight the current (or next) month, but rather the days between now and the same date of next month. This could be what you want but it is not clear. So it would highlight from 6 September to 5 October rather than 1 Sep to 30 Sep for example. If you want to highlight calendar months, don't use the DAY part of your start data, just use a 1 (and for the last day of the month use Month+1, Day=1, and then take -1 from the resulting DATE)

Do you want to highlight lots of cells based on this one single start date cell? Or are you trying to highlight rows based on a different value in each row (in column 5)? If the latter, you need to drop the $ from the 5 so you have a relative reference, but make sure you edit this conditional formula while in a cell on row 5 otherwise you are referencing a row above/below for each case.