Conditional Formating

P

PaolaAndrea

I have a calendar and I want to change the color for the cell that are Sat or
Sun. The date is a formula (=today()) and I want to have sat or sun come up
with a different color, not only where the date is but also some lines that
go down the line.

Please Help.

Sincerely,

PaolaAndrea
 
D

Don Guillett

format>conditional format>formula is
=weekday(a1)=1
condition 2
=weekday(a1)=7
 
M

Max

Here's one interp on what you're after ..

Assume you have sequential dates in A1:G1
say: 17-May-2005 in A1 to ... 23-May-2005 in G1
and you have 4 rows of data below each date
which are to be colored together if the dates in A1:G1
are either Sat or Sun

Select A1:G5
Click Formatting > Conditional Formatting
Under Condition 1, make the setting as:
Formula is| =OR(WEEKDAY(A$1,2)=6,WEEKDAY(A$1,2)=7)
Click the Format button > Patterns tab > Light Green? > OK
Click OK at the main dialog

You'll see that cells E1:F5 will be colored Light Green
(E1:F1 contains the dates 21-May-2005 / 22-May-2005
which are Sat / Sun)
 
P

PaolaAndrea

OK, I have on C1 to C14 the formula =today(), =today()+1, =today()+3 etc.
Where the value is Sat or Sun I want to have the cell and the 10 next cell
below to come up yellow. I enter your formulat but it reads: NAME?

How can I have excel recognize the weekend on cells c1 to c14 and hightlight
the weekend with a different color?

Thank you for your help.

Sincerely,

PaolaAndrea
 
B

Bob Phillips

The 10 below, that could include 2 weekends, If you mean 10 right, try this

select C1:L14
go into CF
add the formula =WEEKDAY($C1,2)>5

Also,
if you have a language version of Excel, you might need DIASEM instead of
WEEKDAY
 
P

PaolaAndrea

Thank you.

I understand your formula with the exception of the 2. A$1,2=6 and A$1,2=7

6 is saturday, 7 is sunday, A is the column name, 1 is the cell number, but
what about 2? what does mean?

Thank you for your help.

Sincerely,

PaolaAndrea
 
D

Don Guillett

You may want to take the opportunity to look at the HELP index for WEEKDAY
in hopes of answering your own question.
 
M

Max

Thank you.
Click to expand...

You're welcome !
I understand your formula with
the exception of the 2. ..
Click to expand...

In "WEEKDAY(A$1,2)", the "2" is
the Return_type (from Excel's Help)

There are 3 choices to use: 1 (or omitted), 2, 3

1 [or omitted] will number 1 (Sunday) through 7 (Saturday).
2 numbers 1 (Monday) through 7 (Sunday).
3 numbers 0 (Monday) through 6 (Sunday).

I just prefer using "2" as it seems more "natural" to regard Mon as "1",
Tues as "2" ... Sun as "7"
 
M

Max

Bob Phillips said:
=OR(WEEKDAY(A$1,2)=6,WEEKDAY(A$1,2)=7)
=WEEKDAY(A$1,2)>5
<vbg>
Click to expand...

Urrgh .. you got me there ! Much neater. Thanks.
I forgot a week has a max of 7 days & Sat/Sun are consecutive ! <bg>
 
Top