Conditional formatting based on formula where cell references can change

[amarkham]

Hi,

I have dates along row 8 and I want the rows above it (rows 3-7) to be conditionally formatted based on the date in row 8, for each column. Easy enough. I can do =AND(C$8>=DATE(2022,1,1),C$8<=DATE(2022,12,31)) in the conditional formatting formula and it works properly for all cells C3:P7. Now here is the kicker. I want to copy and paste this conditional formatting not just for C3:P7 but also C10:P13 (checking the date in row 14) eg I also have dates in row 14 and I want rows 11-14 to be conditionally formatted to look at row 14. And this repeats every 6th row enough times that I want to make the referenced cell in the formula to be based on a formula itself.

If I try =AND(INDIRECT(ADDRESS(CEILING.MATH(ROW()-2,6,0)+2,COLUMN()))<=DATE(2022,1,1),INDIRECT(ADDRESS(CEILING.MATH(ROW()-2,6,0)+2,COLUMN()))<=DATE(2022,12,31)) it doesn't work properly. I think it doesn't like indirect in conditional formatting formulas but I'm not sure. Any ideas how I can accomplish my objective?

Thanks.

[Bernie Deitrick]

Use the formula

=YEAR(INDEX(C:C,INT((ROW()+3)/6)*6+2))=2022

[amarkham]

You're the man - thanks, Bernie!