I have cells that have a formula that displays the day of the week based on a
date elsewhere. I would like to shade a portion of the saturday and sunday
columns and have it update automatically when the month changes. I need a
pretty descriptive answer as I am still learning the ways of Excel. Please
help as soon as you can.

Thank you

If you have "Sunday" in A1,
Select the cells you want to be shaded (say A2:C10). Go to conditional
format, use "formula is" and enter

=$A$1="Sunday"
and choose format and select a grey color. Then if A1 says sunday, those
cells will take on that color.


"Justin" wrote in message
...
I have cells that have a formula that displays the day of the week based
on a
date elsewhere. I would like to shade a portion of the saturday and sunday
columns and have it update automatically when the month changes. I need a
pretty descriptive answer as I am still learning the ways of Excel. Please
help as soon as you can.

Thank you

Formatconditional formatting, formula is and use

=WEEKDAY(A1,2)5

click format and select patterns

click OK twice

where A1 is the cell you want to format

Regards,

Peo Sjoblom

"Justin" wrote:

I have cells that have a formula that displays the day of the week based on a
date elsewhere. I would like to shade a portion of the saturday and sunday
columns and have it update automatically when the month changes. I need a
pretty descriptive answer as I am still learning the ways of Excel. Please
help as soon as you can.

Thank you

Hi
are your values date values or string values

--
Regards
Frank Kabel
Frankfurt, Germany
"Justin" schrieb im Newsbeitrag
...
I have cells that have a formula that displays the day of the week based on
a
date elsewhere. I would like to shade a portion of the saturday and sunday
columns and have it update automatically when the month changes. I need a
pretty descriptive answer as I am still learning the ways of Excel. Please
help as soon as you can.

Thank you

Thanks for the quick responses. Perhaps I need to clarify. Here is how it
works. I have a cell that is referenced to 1/1/2005 (I6). The cell has this
formula: =TEXT((I6),"ddd"). The next cell over is: =TEXT((I6)+1,"ddd") and so
on.

What I would like to do is since the days update automatically based on the
starting date (I6) I would like the columns to either be shaded (if the day
is Sat or Sun) or just remain plain. I need to do conditional formatting as I
will be locking the sheet and only be allowed to enter numbers into the cells
below the days. I need the cells to change color based on the day - not what
I enter into each cell.

Thanks for your help!


"Justin" wrote:

I have cells that have a formula that displays the day of the week based on a
date elsewhere. I would like to shade a portion of the saturday and sunday
columns and have it update automatically when the month changes. I need a
pretty descriptive answer as I am still learning the ways of Excel. Please
help as soon as you can.

Thank you

The formula would be

=OR(I$7="Sun",I$7="Sat")

if row 7 contained "Sat" "Sun" "Mon" etc. and you would select the range of
cells that you want shaded before entering this formula in the conditional
formatting part.


"Justin" wrote in message
...
Thanks for the quick responses. Perhaps I need to clarify. Here is how it
works. I have a cell that is referenced to 1/1/2005 (I6). The cell has
this
formula: =TEXT((I6),"ddd"). The next cell over is: =TEXT((I6)+1,"ddd") and
so
on.

What I would like to do is since the days update automatically based on
the
starting date (I6) I would like the columns to either be shaded (if the
day
is Sat or Sun) or just remain plain. I need to do conditional formatting
as I
will be locking the sheet and only be allowed to enter numbers into the
cells
below the days. I need the cells to change color based on the day - not
what
I enter into each cell.

Thanks for your help!


"Justin" wrote:

I have cells that have a formula that displays the day of the week based
on a
date elsewhere. I would like to shade a portion of the saturday and
sunday
columns and have it update automatically when the month changes. I need
a
pretty descriptive answer as I am still learning the ways of Excel.
Please
help as soon as you can.

Thank you

That was exactly what I needed. Any thoughts on how to highlight the column -
perhaps a second conditional format for if the column is the current date? I
tried the TODAY() function and it didn't work. Might not be possible with how
the sheet is set up. The date cell just has a formula plus 1, so it doesn't
have an actual date. Just thought highlighting the column would be neat.
Thanks again for your help.

"Dave R." wrote:

The formula would be

=OR(I$7="Sun",I$7="Sat")

if row 7 contained "Sat" "Sun" "Mon" etc. and you would select the range of
cells that you want shaded before entering this formula in the conditional
formatting part.


"Justin" wrote in message
...
Thanks for the quick responses. Perhaps I need to clarify. Here is how it
works. I have a cell that is referenced to 1/1/2005 (I6). The cell has
this
formula: =TEXT((I6),"ddd"). The next cell over is: =TEXT((I6)+1,"ddd") and
so
on.

What I would like to do is since the days update automatically based on
the
starting date (I6) I would like the columns to either be shaded (if the
day
is Sat or Sun) or just remain plain. I need to do conditional formatting
as I
will be locking the sheet and only be allowed to enter numbers into the
cells
below the days. I need the cells to change color based on the day - not
what
I enter into each cell.

Thanks for your help!


"Justin" wrote:

I have cells that have a formula that displays the day of the week based
on a
date elsewhere. I would like to shade a portion of the saturday and
sunday
columns and have it update automatically when the month changes. I need
a
pretty descriptive answer as I am still learning the ways of Excel.
Please
help as soon as you can.

Thank you

Hmm. Would you want to highlight a range if the current weekday (returned by
Today()) is the same as the range to be highlighted?

If the column headers are only text like "Sun" and "Sat" then there's no
date associated with it. You can reference the cell that contains the date.

Or you could, instead of labeling with"Sat" and "Sun", use some formula that
spits out the date, like "Thu 12/16/04". Then you can use the today()
function in the conditional format comparing to the date part of the cell
content, and highlight that way.

You can change your TEXT formula (say it's in B1 with an actual date in A1)
to:
=TEXT(A1,"ddd "&"m/d/yy") -- which will return "Thu 12/16/04"
then in conditional formatting use:
=TODAY()=DATEVALUE(MID(B1,FIND(" ",C1),9))
which will be TRUE on today's date.




"Justin" wrote in message
...
That was exactly what I needed. Any thoughts on how to highlight the
column -
perhaps a second conditional format for if the column is the current date?
I
tried the TODAY() function and it didn't work. Might not be possible with
how
the sheet is set up. The date cell just has a formula plus 1, so it
doesn't
have an actual date. Just thought highlighting the column would be neat.
Thanks again for your help.

"Dave R." wrote:

The formula would be

=OR(I$7="Sun",I$7="Sat")

if row 7 contained "Sat" "Sun" "Mon" etc. and you would select the range
of
cells that you want shaded before entering this formula in the
conditional
formatting part.


"Justin" wrote in message
...
Thanks for the quick responses. Perhaps I need to clarify. Here is how
it
works. I have a cell that is referenced to 1/1/2005 (I6). The cell has
this
formula: =TEXT((I6),"ddd"). The next cell over is: =TEXT((I6)+1,"ddd")
and
so
on.

What I would like to do is since the days update automatically based
on
the
starting date (I6) I would like the columns to either be shaded (if
the
day
is Sat or Sun) or just remain plain. I need to do conditional
formatting
as I
will be locking the sheet and only be allowed to enter numbers into
the
cells
below the days. I need the cells to change color based on the day -
not
what
I enter into each cell.

Thanks for your help!


"Justin" wrote:

I have cells that have a formula that displays the day of the week
based
on a
date elsewhere. I would like to shade a portion of the saturday and
sunday
columns and have it update automatically when the month changes. I
need
a
pretty descriptive answer as I am still learning the ways of Excel.
Please
help as soon as you can.

Thank you

Instead of creating text from the date, you could format the date to
show the weekday.

For example, instead of: =TEXT((I6),"ddd")
use: =I6

Select the cell, and choose FormatCells
On the Number tab, select the Custom category
In the text box, type: ddd
Click OK

In the adjacent cells, use a formula that adds 1 to the date in the
previous cell. For example: =B3 + 1
Format all the cells as "ddd"

Then, you can use conditional formatting to highlight the column that
contains the current date.


Justin wrote:
That was exactly what I needed. Any thoughts on how to highlight the column -
perhaps a second conditional format for if the column is the current date? I
tried the TODAY() function and it didn't work. Might not be possible with how
the sheet is set up. The date cell just has a formula plus 1, so it doesn't
have an actual date. Just thought highlighting the column would be neat.
Thanks again for your help.

"Dave R." wrote:


The formula would be

=OR(I$7="Sun",I$7="Sat")

if row 7 contained "Sat" "Sun" "Mon" etc. and you would select the range of
cells that you want shaded before entering this formula in the conditional
formatting part.


"Justin" wrote in message
...

Thanks for the quick responses. Perhaps I need to clarify. Here is how it
works. I have a cell that is referenced to 1/1/2005 (I6). The cell has

this

formula: =TEXT((I6),"ddd"). The next cell over is: =TEXT((I6)+1,"ddd") and

so

on.

What I would like to do is since the days update automatically based on

the

starting date (I6) I would like the columns to either be shaded (if the

day

is Sat or Sun) or just remain plain. I need to do conditional formatting

as I

will be locking the sheet and only be allowed to enter numbers into the

cells

below the days. I need the cells to change color based on the day - not

what

I enter into each cell.

Thanks for your help!


"Justin" wrote:


I have cells that have a formula that displays the day of the week based

on a

date elsewhere. I would like to shade a portion of the saturday and

sunday

columns and have it update automatically when the month changes. I need

a

pretty descriptive answer as I am still learning the ways of Excel.

Please

help as soon as you can.

Thank you





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks everybody that helped - I know have a very user friendly excel sheet.

"Debra Dalgleish" wrote:

Instead of creating text from the date, you could format the date to
show the weekday.

For example, instead of: =TEXT((I6),"ddd")
use: =I6

Select the cell, and choose FormatCells
On the Number tab, select the Custom category
In the text box, type: ddd
Click OK

In the adjacent cells, use a formula that adds 1 to the date in the
previous cell. For example: =B3 + 1
Format all the cells as "ddd"

Then, you can use conditional formatting to highlight the column that
contains the current date.


Justin wrote:
That was exactly what I needed. Any thoughts on how to highlight the column -
perhaps a second conditional format for if the column is the current date? I
tried the TODAY() function and it didn't work. Might not be possible with how
the sheet is set up. The date cell just has a formula plus 1, so it doesn't
have an actual date. Just thought highlighting the column would be neat.
Thanks again for your help.

"Dave R." wrote:


The formula would be

=OR(I$7="Sun",I$7="Sat")

if row 7 contained "Sat" "Sun" "Mon" etc. and you would select the range of
cells that you want shaded before entering this formula in the conditional
formatting part.


"Justin" wrote in message
...

Thanks for the quick responses. Perhaps I need to clarify. Here is how it
works. I have a cell that is referenced to 1/1/2005 (I6). The cell has

this

formula: =TEXT((I6),"ddd"). The next cell over is: =TEXT((I6)+1,"ddd") and

so

on.

What I would like to do is since the days update automatically based on

the

starting date (I6) I would like the columns to either be shaded (if the

day

is Sat or Sun) or just remain plain. I need to do conditional formatting

as I

will be locking the sheet and only be allowed to enter numbers into the

cells

below the days. I need the cells to change color based on the day - not

what

I enter into each cell.

Thanks for your help!


"Justin" wrote:


I have cells that have a formula that displays the day of the week based

on a

date elsewhere. I would like to shade a portion of the saturday and

sunday

columns and have it update automatically when the month changes. I need

a

pretty descriptive answer as I am still learning the ways of Excel.

Please

help as soon as you can.

Thank you





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html