Conditional Formatting Looking through dynamic range

[NeoFlex]

Hey guys, I am building an excel scheduling calendar and can't figure out one of the steps. I need to look through 21 days into the future to see if the first cell matches with any of the 21 future cells...in the same row. So May 1st look from May1st (D2) through May21st(X2) and the same thing for the other rows. Then it would need to look from May2nd through May22nd and so on and so forth.

I've attached an example copy of the workbook

Any help would be much appreciated.

Thanks and KCCO!

[martindwilson]

cf with =COUNTIF(OFFSET(D2,0,1,,21),D2)

[NeoFlex]

Ok, well my logic didn't exactly pan out. I tried to adapt this but it went down burning (It didn't like -21). I actually need this to look 21 days in the past and if it finds a match then the cell that's in the future (aka the later one) would turn red.

[mccombsj]

Try this:

=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-21)=INDIRECT(ADDRESS(ROW(),COLUMN()))

[martindwilson]

try in d2
=ISNUMBER(MATCH(D2,OFFSET(D2,0,MAX(-COLUMN(A1)-1,-21),,MIN(COLUMN(A1)+1,21)),0))

[NeoFlex]

Martin, that's perfect! Thank you so much.