Conditional Formatting Formula

[Abbie]

I have a spreadsheet containing dates, I need to shade the cells with dates that are 'in the past' (dates are, once again, taken from another worksheet using VLOOKUP), i know this is done using conditional formatting, more than likely 'formula is' but can't quite work it out.

I manually enter todays date into cell H1
Cells within columns are in blocks of 4;
1 - Forecast Date
2 - Actual Date
3 - Planned Early Start
4 - Planned Late Finish
(3 & 4 are irrelevant)

Now, for example, Column R

In cell R18 i have a future date (e.g 28-Feb-09), cells R19, R20 & R21 are Blank.

In cell R23 i have a past date (e.g 20-Dec-08), cells R22, R24 & R25 are Blank

Cells R18->R21 do not need to be shaded as they're in the future, on the otherhand ... Cells R22->R25 (the whole block of 4) DO need to be shaded because the "Actual Date", is in the past, regardless of what may be input into the other 3 cells, they all need to be shaded.

Apologies if that doesn't make much sense. All help would be much appreciated!

Abbie
<!-- / message -->

 

[nbrcrunch]

since you mentioned R23...

=today()>R23

 

[Yard]

Abbie,

I would say that your data layout makes things like this very difficult! nbrcrunch has given you a start (although I think you'll need =today()>R$23 since R23 will determine the formatting for various cells within the column.

Are you in a position to lay out your data in rows and columns where each column has only one formula in it (one formula but multiple rows, I mean)?

 

[Abbie]

Thankyou for advice so far, I've tried this but can't seem to get it to work properly. Although it does shade the cells in, it will only do the top three and not the bottom one, also when conditional format is applied to dates that are in the future, it shades those in aswell, when i just need past dates highlighted.

Finally, as this is quite a large spreadsheet, is there any way i can highlight all data and apply to the whole spreadsheet or do i have to do each block of 4 separately? (there are 4 columns of 55 blocks of 4 - 220 rows in each column. Thats a total of 220 individual blocks and it would take quite a while to highlight each one and apply conditional format separately??)

Thanks in advance for your help.

Abbie

 

[Yard]

Finally, as this is quite a large spreadsheet, is there any way i can highlight all data and apply to the whole spreadsheet or do i have to do each block of 4 separately? (there are 4 columns of 55 blocks of 4 - 220 rows in each column. Thats a total of 220 individual blocks and it would take quite a while to highlight each one and apply conditional format separately??)

That's what I was driving at when I said

I would say that your data layout makes things like this very difficult!

There may be fiddly ways of doing it.

i) can you use Autofilter to separate out the blocks of data that you need?

ii) for each block of 4 is there a cell in another colunm which tells you what each 1 of the 4 represents?

Can you give us an example layout of a few blocks of 4, including row and column headers and an indication of which cells you want shaded and how.