Conditional Formatting with a moving Range

[Shytott]

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

[protonLeah]

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.

[Shytott]

Hi ProtonLeah
Thats brilliant, it works a treat!! Thankyou.
And as ever in these situations, having seen the result, Ive tried to enhance it by giving the user the choice of having the white out or not, so I tried adding your code to a simple If statement to the effect that if there was a "1" in B33, then execute your code, if not, then dont. So I tried various combinations of adding it eg:
=if($B$33=1,=ROW()>MATCH($M$28,$D:$D,0),0) in Cond Formatting but Excel didnt like it. Can you help with this wee enhancement?
Many Thanks
Shytott

[NBVC]

Try

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

[Shytott]

Thanks NBVC
I pasted your code into Cond Formatting, but still got the same error message

Shy

[NBVC]

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

[Shytott]

Hi NBVC
Nope, just tried it again, exactly as you have it in your first reply, no quotes and its still failing. Have you tried entering it on my Tester1 spreadsheet?

Shy

[NBVC]

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...

[NBVC]

Some extra stuff that could be removed at the end

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

[Shytott]

WHOO HOOOO!
Nice one Thanks NBVC
Once again this site proves its worth - thanks for sharing your knowledge, much appreciated :-)

Shy