How do I do conditional format based on a cell with a formula?

[Justin]

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

 

[Dave R.]

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.

 

[Peo Sjoblom]

Format>conditional 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

 

[Frank Kabel]

Hi
are your values date values or string values

 

[Justin]

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!

 

[Dave R.]

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]

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

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.

 

[Debra Dalgleish]

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 Format>Cells
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]

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