shade cells

S

superkopite

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
 
D

daddylonglegs

Select E1:N1 with E1 active cell

Format > Conditional Formatting > formula is

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

Apply desired formatting
 
B

Bill Kuunders

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" <[email protected]>
wrote in message
news:[email protected]...
 
B

Bob Phillips

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

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

CF:
Format>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)

"superkopite" <[email protected]>
wrote in message
news:[email protected]...
 
S

superkopite

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
 
D

daddylonglegs

superkopite said:
=(E1>=$B2)*(E1<=$C2) works great

but why does this not work;
=($E$1>=$B2)*($E$1<=$C2)
Click to expand...

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
 
S

superkopite

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
 
D

daddylonglegs

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?
 
S

superkopite

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?
 
D

daddylonglegs

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

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
 
S

superkopite

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
 
Top