Grey out cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Angela1979 - 07 Mar 2007 13:38 GMT

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.

Reply to this Message

Max - 07 Mar 2007 14:06 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hi all,
>
[quoted text clipped - 31 lines]
> Anything that doesn't please ask.
> Thanks everyone for taking time to help.

Reply to this Message

Bernard Liengme - 07 Mar 2007 14:12 GMT

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

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Select B2:E13 (with B2 active), then apply CF using the formula:
> =AND($A2>=B$1,$A2<C$1)
[quoted text clipped - 39 lines]
>> Anything that doesn't please ask.
>> Thanks everyone for taking time to help.

Reply to this Message

Max - 07 Mar 2007 14:21 GMT

Good point, Bernard. Thanks !

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

Reply to this Message

Angela1979 - 07 Mar 2007 15:07 GMT

> Good point, Bernard. Thanks !
> --
[quoted text clipped - 12 lines]
>
> - Show quoted text -

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

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

Reply to this Message

Max - 07 Mar 2007 15:13 GMT

Welcome, Angela.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

Reply to this Message

Angela1979 - 07 Mar 2007 16:16 GMT

> Welcome, Angela.
> --
[quoted text clipped - 5 lines]
>
> - 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.

Reply to this Message

Max - 08 Mar 2007 00:56 GMT

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

Reply to this Message

Max - 08 Mar 2007 01:10 GMT

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Angela1979 - 08 Mar 2007 11:53 GMT

> A further thought ..
>
[quoted text clipped - 16 lines]
> 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

Reply to this Message

Max - 08 Mar 2007 12:45 GMT

> 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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hi Max,
>
[quoted text clipped - 13 lines]
> Sorry could be something I am mising, I have tried a number of
> different things but can't seem to crack it.

Reply to this Message

Angela1979 - 08 Mar 2007 13:55 GMT

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

Reply to this Message

Angela1979 - 08 Mar 2007 13:59 GMT

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

Reply to this Message

Max - 08 Mar 2007 21:16 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>
> I tried to tell you what column was what but the post hasn't posted
[quoted text clipped - 5 lines]
> Column A contains scheduledreleasedate
> Column B contains catDef

Reply to this Message