Hey guys and gals

this should be a simple problem i just cannot work it out!

In A2 i have a Name i.e James
In B2 i have a start time i.e 07:30
In C2 i have a finish time i.e 16:00
In E1 to AN1 i have times ranging from 06:00 to 23:30

What i need is the following:
The cells in E2 to AN1 to be shaded from the B2 start time to the C2
finish time

like i say this doesn't sound to difficult and and i am sure there is a
very easy way to do it but i just can work it out

cheers

James


--
superkopite
------------------------------------------------------------------------
superkopite's Profile: http://www.excelforum.com/member.php...o&userid=29496
View this thread: http://www.excelforum.com/showthread...hreadid=503689

Select E1:N1 with E1 active cell

Format Conditional Formatting formula is

=(E1=$B2)*(E1<=$C2)

Apply desired formatting


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=503689

you truly are the Daddy

Thanks a lot!!


--
superkopite
------------------------------------------------------------------------
superkopite's Profile: http://www.excelforum.com/member.php...o&userid=29496
View this thread: http://www.excelforum.com/showthread...hreadid=503689

Assuming that you want to color the cells between E2 and AN2 and not
AN1.........

Copy the times down from E1 :AN1 to E2:AN2
select the cells E2:AN2
go to format
conditional formatting
use cell value is between B2 and C2
select format...
pattern

if you do not want to show the times in that row you need to
use the normal format......... cells........font to select the font to be
white for all cells
and select the font as well as the pattern to be grey in the conditional
format.

--
Greetings from New Zealand
Bill K



"superkopite"
wrote in message
...

Hey guys and gals

this should be a simple problem i just cannot work it out!

In A2 i have a Name i.e James
In B2 i have a start time i.e 07:30
In C2 i have a finish time i.e 16:00
In E1 to AN1 i have times ranging from 06:00 to 23:30

What i need is the following:
The cells in E2 to AN1 to be shaded from the B2 start time to the C2
finish time

like i say this doesn't sound to difficult and and i am sure there is a
very easy way to do it but i just can work it out

cheers

James


--
superkopite
------------------------------------------------------------------------
superkopite's Profile:
http://www.excelforum.com/member.php...o&userid=29496
View this thread: http://www.excelforum.com/showthread...hreadid=503689

Select E2:AN2, and then use Conditional Formatting with a formula of

=AND($B2<=E$1,$C2=E$1)

CF:
FormatConditional formatting
Change Condition 1 to Formula Is
Add the formula
Select Format
Patterns tab
Pick a colour
OK

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"superkopite"
wrote in message
...

Hey guys and gals

this should be a simple problem i just cannot work it out!

In A2 i have a Name i.e James
In B2 i have a start time i.e 07:30
In C2 i have a finish time i.e 16:00
In E1 to AN1 i have times ranging from 06:00 to 23:30

What i need is the following:
The cells in E2 to AN1 to be shaded from the B2 start time to the C2
finish time

like i say this doesn't sound to difficult and and i am sure there is a
very easy way to do it but i just can work it out

cheers

James


--
superkopite
------------------------------------------------------------------------
superkopite's Profile:
http://www.excelforum.com/member.php...o&userid=29496
View this thread: http://www.excelforum.com/showthread...hreadid=503689

Thanks for all the responses guys once again i am spoilt for choice.

This is most definatley the best forum ever!


Thanks Daddylonglegs

=(E1=$B2)*(E1<=$C2) works great

but why does this not work;
=($E$1=$B2)*($E$1<=$C2)

Cheers

James


--
superkopite
------------------------------------------------------------------------
superkopite's Profile: http://www.excelforum.com/member.php...o&userid=29496
View this thread: http://www.excelforum.com/showthread...hreadid=503689

superkopite Wrote:

=(E1=$B2)*(E1<=$C2) works great

but why does this not work;
=($E$1=$B2)*($E$1<=$C2)


The first one adjusts for each cell so if you look at CF in F1 it
should say

=(F1=$B2)*(F1<=$C2)

but because of the $s around E1 the second remains the same in all
cells, so your whole range E1:N1 will be formatted the same according
to whether E1 is formatted


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=503689

that is what it needs to be as E1 to AO1 is where the time referance is
obtained from.

so even row 4, the formula reads =(E1=$B4)*(E1<=$C4) and that works
but when i drag down obviously the E1 changes to F1

Or have i missed something here?

Cheers

James


--
superkopite
------------------------------------------------------------------------
superkopite's Profile: http://www.excelforum.com/member.php...o&userid=29496
View this thread: http://www.excelforum.com/showthread...hreadid=503689

Now I'm a little confused - you wanted to format E1:AO1 depending on
times in B2 and C2.

Do you want to extend this to other rows?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=503689

sorry to be a pain

yeah i wanted it to extend to other rows

so in row A4

=(E1=$B4)*(E1<=$C4) works fine but,
=($E$1=$B4)*($E$1<=$C4) does not

with my limted knowledge these two formulas are the same until you drag
them are they not?


--
superkopite
------------------------------------------------------------------------
superkopite's Profile: http://www.excelforum.com/member.php...o&userid=29496
View this thread: http://www.excelforum.com/showthread...hreadid=503689

I tried to explain in my earlier post but I may have just confused
things

If you apply this CF formula....

=(E1=$B2)*(E1<=$C2)

..... to a range, e.g. E1:AO1 it automatically adjusts across the
columns. so in G1 this becomes

=(G1=$B2)(G1=$C2) which is what you need to happen

(note that $B2 doesn't change because the $ fixes the column
reference)

You can't use

=($E$1=$B4)*($E$1<=$C4) because although it should work correctly in
E1 it won't work in F1:AO1

perhaps you want

=(E$1=$B4)*(E$1<=$C4)

this fixes the row reference but not the column reference


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=503689

How could i make such a basic error by putting the $ in front of both E
and 1!!!

I thought iwas getting better at this!!!

Thank you very much mate

James


--
superkopite
------------------------------------------------------------------------
superkopite's Profile: http://www.excelforum.com/member.php...o&userid=29496
View this thread: http://www.excelforum.com/showthread...hreadid=503689