software

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: www.excelforum.com/member.php...oamp;userid=29496
View this thread: www.excelforum.com/showthread...hreadid=503689
Select E1:N1 with E1 active cell

Format gt; Conditional Formatting gt; formula is

=(E1gt;=$B2)*(E1lt;=$C2)

Apply desired formatting--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=503689
you truly are the Daddy

Thanks a lot!!--
superkopite
------------------------------------------------------------------------
superkopite's Profile: www.excelforum.com/member.php...oamp;userid=29496
View this thread: www.excelforum.com/showthread...hreadid=503689Assuming 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
quot;superkopitequot; gt;
wrote in message
...
gt;
gt; Hey guys and gals
gt;
gt; this should be a simple problem i just cannot work it out!
gt;
gt; In A2 i have a Name i.e James
gt; In B2 i have a start time i.e 07:30
gt; In C2 i have a finish time i.e 16:00
gt; In E1 to AN1 i have times ranging from 06:00 to 23:30
gt;
gt; What i need is the following:
gt; The cells in E2 to AN1 to be shaded from the B2 start time to the C2
gt; finish time
gt;
gt; like i say this doesn't sound to difficult and and i am sure there is a
gt; very easy way to do it but i just can work it out
gt;
gt; cheers
gt;
gt; James
gt;
gt;
gt; --
gt; superkopite
gt; ------------------------------------------------------------------------
gt; superkopite's Profile:
gt; www.excelforum.com/member.php...oamp;userid=29496
gt; View this thread: www.excelforum.com/showthread...hreadid=503689
gt;
Select E2:AN2, and then use Conditional Formatting with a formula of

=AND($B2lt;=E$1,$C2gt;=E$1)

CF:
Formatgt;Conditional 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)

quot;superkopitequot; gt;
wrote in message
...
gt;
gt; Hey guys and gals
gt;
gt; this should be a simple problem i just cannot work it out!
gt;
gt; In A2 i have a Name i.e James
gt; In B2 i have a start time i.e 07:30
gt; In C2 i have a finish time i.e 16:00
gt; In E1 to AN1 i have times ranging from 06:00 to 23:30
gt;
gt; What i need is the following:
gt; The cells in E2 to AN1 to be shaded from the B2 start time to the C2
gt; finish time
gt;
gt; like i say this doesn't sound to difficult and and i am sure there is a
gt; very easy way to do it but i just can work it out
gt;
gt; cheers
gt;
gt; James
gt;
gt;
gt; --
gt; superkopite
gt; ------------------------------------------------------------------------
gt; superkopite's Profile:
www.excelforum.com/member.php...oamp;userid=29496
gt; View this thread: www.excelforum.com/showthread...hreadid=503689
gt;

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

This is most definatley the best forum ever!Thanks Daddylonglegs

=(E1gt;=$B2)*(E1lt;=$C2) works great

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

Cheers

James--
superkopite
------------------------------------------------------------------------
superkopite's Profile: www.excelforum.com/member.php...oamp;userid=29496
View this thread: www.excelforum.com/showthread...hreadid=503689
superkopite Wrote:
gt;
gt; =(E1gt;=$B2)*(E1lt;=$C2) works great
gt;
gt; but why does this not work;
gt; =($E$1gt;=$B2)*($E$1lt;=$C2)
gt;

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

=(F1gt;=$B2)*(F1lt;=$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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: 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 =(E1gt;=$B4)*(E1lt;=$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: www.excelforum.com/member.php...oamp;userid=29496
View this thread: 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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=503689
sorry to be a pain

yeah i wanted it to extend to other rows

so in row A4

=(E1gt;=$B4)*(E1lt;=$C4) works fine but,
=($E$1gt;=$B4)*($E$1lt;=$C4) does not

with my limted knowledge these two formulas are the same until you drag
them are they not?--
superkopite
------------------------------------------------------------------------
superkopite's Profile: www.excelforum.com/member.php...oamp;userid=29496
View this thread: www.excelforum.com/showthread...hreadid=503689