Conditional Formatting with a moving Range

Hi All,
I have a Conditional Formatting query where in essence I am needing to ‘make invisible’ a range of data by changing the font and background fill to white (and at the same time nullifying the effects of a number of other Conditional Formats). On the surface, this seems an easy thing to achieve, but the problem is, the range I need to apply this CF is a moving range and is dependant on a user changeable date.

Description of worksheet:
The sheet is managing shifts worked over 1 year by 21 people. The top 33 rows are a frozen (and collapsing) display area below which is a matrix of 365 days down and 21 people across. The TODAY button uses a macro to scan the dates and display todays entry directly beneath the freeze line of row 33. The date in M28 can be entered manually and after hitting enter, that dates data is now displayed under the freeze line. Also, using the spin button will increment or decrement the date in M28 moving the entire matrix up or down.

My query..........
What I’m hoping to achieve, is to display ONLY the data selected by the date in M28 (ie that shown under the freeze line, and for all remaining rows to be ‘whited out’, but as the user increments/decrements M28, then the “whiteout” range also needs to increment/decrement. I have whited out a second worksheet “example” as if I had selected 18 Jan (this sheet will not move as there are no buttons, but the data is still there under the whiteout).

I hope this is not TOO unclear, but I would be grateful if anyone could suggest a solution or even tell me if this is not possible – or indeed any other way of achieving the same result ( I had thought of hiding the rows below the freeze line, but the freeze/unfreeze operation would probably be too jerky.

Many Thanks in anticipation

Shytott

Use the the formula below as the first Conditional Format and check the STOP IF TRUE box:

Please Login or Register  to view this content.

Try

=AND($B$33=1,ROW()>MATCH($M$28,$D:$D,0),0))

First, make sure the conditional format formula is not enclosed in quotes...

To tell you the truth, no I have not... I just based my answer on the formula you were trying to create to do the job.... I will try now...

Some extra stuff that could be removed at the end

=AND($B$33=1,ROW()>MATCH($M$28,$D:$D,0))