Conditional formatting does not work or formula is incorrect

1
votes

My formula checks for value in 7 cells merged together and outputs TRUE if value meets the condition. Formula works fine when I apply it to every cell individually as shown on the image, but when I try to use the same formula in conditional formatting to highlight range of cells that meets criteria, nothing happens.

Here is my formula:

=IF($B$9<>1,FALSE,AND(INDIRECT(ADDRESS(12,COLUMN(Z$11)-IF(WEEKDAY(Z$11)=1,6,(WEEKDAY(Z$11)-2))))>=$L14,INDIRECT(ADDRESS(12,COLUMN(Z$11)-IF(WEEKDAY(Z$11)=1,6,(WEEKDAY(Z$11)-2))))<=$R14))

enter image description here

Basically what my formula does is checks if date in the merged cell is either grater or equal to the date of the other cell (Start Date) and smaller or equal to the End Date, which is also defined in another cell.

I cannot seem to get it to work.

Please help

1
excelexcel-formulaconditional-formatting

1 Answers

1
votes

I don't know why you need that formula(maybe I misunderstood) but an example is as follows;

Edited:

Maybe you can use a dummy (not merged) row.enter image description here

Row 5 is conditionally formatted based on row 7th values. Please note that,

1) Formula in the 7th row is seen in the formula bar.

2) Conditionally format range(not shown in the pic) is 5th row.