Grey out cell

[Angela1979]

Hi all,

My brain hurts from trying to work this one out, I'm no wizard like
most of you on here.
Anyway here's my problem...... hope someone can help.

scheduledreleasedate 14/03/07 21/03/07 28/03/07 04/04/07
09 Jul 07
30 Apr 07
05 Apr 07
01 Apr 07
29 Mar 07
26 Mar 07
16 Mar 07 Grey
16 Mar 07 Grey
16 Mar 07 Grey
16 Mar 07 Grey
15 Mar 07 Grey
08 Mar 07

The dates along the top row are todays date +7days, +14days, +21days,
etc.
I need to ... if the scheduledreleasedate is for example 15 Mar 07 I
would need to put a greyed out cell under the 14/03/07 cell in line
with the 15th March row. Or just write the word grey.

Basically, I need to put a grey cell on the row under the appropiate
date, for example if the scheduledreleasedate is between 14/03 and
20/03 a grey cell would be inserted under the 14/03 column, if the
scheduledreleasedate is 22/03 a grey cell would be inserted under the
21/03 column.

I hope this makes sense to people.
Anything that doesn't please ask.
Thanks everyone for taking time to help.

 

[Max]

Select B2:E13 (with B2 active), then apply CF using the formula:
=AND($A2>=B$1,$A2<C$1)
Format with gray fill > OK out

The above is the CF equivalent to placing this in B2:
=IF(AND($A2>=B$1,$A2<C$1),"grey","")
then copying B2 across / down to E13

 

[Bernard Liengme]

To save problems with the last column use
=AND($A2>=B$1,$A2<B$1+7)
best wishes

 

[Max]

Good point, Bernard. Thanks !

 

[Angela1979]

Good point, Bernard. Thanks !
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---





- Show quoted text -

.....................................................................................................................................................

You guys are fantastic thanks very much for all your help.

 

[Max]

Welcome, Angela.

 

[Angela1979]

Welcome, Angela.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik





- Show quoted text -

Hi guys,

I'm back again sorry.
With reference to the above another column has now been added - just
when I thought it was complete!
Column D
Contains either BAU or Project.
I need to write if cells D contains Project cell to be black not
grey? I have no idea if this is possible.

 

[Max]

We can structure the CF to evaluate for cells containing "Project"
ahead of the earlier "dates within" criteria by setting it as
Condition 1, then Condition 2.

Try it this way ..

Select B2:E13 (B2 active), then apply the CF settings:

Cond1: =B2="Project"
Format: Black Fill / White Font

Cond2: =AND($A2>=B$1,$A2<B$1+7)
Format: Gray Fill

 

[Max]

A further thought ..

if Cond1 is to be evaluated simultaneous with Cond2, ie you want cells
containing "Project" to be highlighted in black, but only if these
cells also satisfy the earlier "dates within" criteria, then just
change the previous:

Cond1: =B2="Project"

to

Cond1: =AND(B2="Project",$A2>=B$1,$A2<B$1+7)
Format: Black Fill / White Font

Cond2 (no change)

 

[Angela1979]

A further thought ..

if Cond1 is to be evaluated simultaneous with Cond2, ie you want cells
containing "Project" to be highlighted in black, but only if these
cells also satisfy the earlier "dates within" criteria, then just
change the previous:


to

Cond1: =AND(B2="Project",$A2>=B$1,$A2<B$1+7)
Format: Black Fill / White Font

Cond2 (no change)
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Hi Max,

Thanks so much for you help.
I have added the above as instructed however this does not work.

Cond1: =AND(B2="Project",$A2>=B$1,$A2<B$1+7)

Format: Black Fill / White Font

I have changed this to:
Cond1: =AND(B2:B1000="Project",$A2>=B$1,$A2<B$1+7)

Format: Black Fill / White Font

as the word Project could be anywhere in the Column.
Sorry could be something I am mising, I have tried a number of
different things but can't seem to crack it.

Thanks again

 

[Max]

I have changed this to:

Cond1: =AND(B2:B1000="Project",$A2>=B$1,$A2<B$1+7)

Ahh, don't change it, it works as given earlier <g>

Here's the full steps to re-try it, assuming your range to be
conditionally formatted is B2:E1000

Select B2:E1000 (B2 active), then apply the CF settings:

Cond1: =AND(B2="Project",$A2>=B$1,$A2<B$1+7)
Format: Black Fill / White Font

Cond2: =AND($A2>=B$1,$A2<B$1+7)
Format: Gray Fill

Let me know here how it goes for you

 

[Angela1979]

Hi Max,

Sorry to be back again.

It won't work, I think it may be something I am doing wrong but I
can't for the life of me work this out.
Here is full details

A B C D
E F G H
I J K

Scheduled catDef 19/03/07 26/03/07 02/04/07 09/04/07 16/04/07 23/04/07
30/04/07 07/05/07 14/05/07
01 Mar 07Project
07 Mar 07 Project
08 Mar 07 Project
15 Mar 07 Project
16 Mar 07 Project
16 Mar 07 Project
16 Mar 07 Project
16 Mar 07 Project
26 Mar 07 Project
29 Mar 07 Project
01 Apr 07 Project
05 Apr 07 BAU
30 Apr 07 BAU
09 Jul 07 BAU



I have added the following conditions
Condition 1
=and(b2="Project",$a2>=c$1,$a2<d$1) Format Black

Condition 2
=and($a2>c$1,$a2<d$1)



Sorry for being a pain
..................................

 

[Angela1979]

I tried to tell you what column was what but the post hasn't posted
correctly
For your information

Row 1 contains titles and dates

Column A contains scheduledreleasedate
Column B contains catDef

 

[Max]

Angela,

Nothing will happen in col B as there's no date in the header cell B1. If
you want the word "Project" to be highlighted by CF in col B as it is right
now, simply add a 3rd condition: =B2="Project" with similar trigger
formatting as Cond1. So your CF conditons set up would be:

Cond1: =AND(B2="Project",$A2>=B$1,$A2<B$1+7)
Format: Black Fill / White Font

Cond2: =AND($A2>=B$1,$A2<B$1+7)
Format: Gray Fill

Cond3: =B2="Project"
Format: Black Fill / White Font