Formula is TRUE as worksheet formula but does not format cell as CF rule

[6StringJazzer]

Stripped down version (original is part of a calendar). The formula is TRUE in all the formatted cells. But the same formula used as a CF rule is not formatting the cells.

I suspect I am missing something obvious but it's so obvious I can't see it. Both screen shots and file attached.

CF oddity.JPG

CF oddity2.jpg

[AliGW]

I think you might need to fix C5:

C$5

[6StringJazzer]

That reference is for the target cell. It must be a relative reference based on how the formula works. In the full sheet the large square is replicated, so the formula should work in any large square to check the value of the cell in the upper-left corner. If the row is an absolute reference then ROW will always be the same and the offset will not work. I tried this change anyway but the CF rule still did not work.

[Fluff13]

I seem to remember coming across this before & CF does not like offset in an AND function. Do you need to check is the column is odd?
Assuming the number will always be in an even numbered column, you should be able to just use the Offset on it's own.

[6StringJazzer]

I am trying to come up with a single formula that will work in all columns. The odd columns have a different criterion than the even columns; this example is just for the odd columns. I suppose I could just give in and use two rules.

When I remove the AND and the ISODD condition, the rule does work.

[protonLeah]

=isodd(column(c5))*(offset(c5,-mod(row(c5)-5,7),-1)=19)?

[6StringJazzer]

=isodd(column(c5))*(offset(c5,-mod(row(c5)-5,7),-1)=19)?

Brilliant! That does work. I have an aversion to using boolean values in arithmetic so this never occurred to me, but it's the perfect time to use it.

[6StringJazzer]

Full problem and full solution for anyone interested

https://www.excelforum.com/excel-for...endar-day.html