Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
PaolaAndrea
 
Posts: n/a
Default Conditional Formating

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
  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

=WEEKDAY(A1,2)5

will be true for Sat or Sun

--
HTH

Bob Phillips

"PaolaAndrea" wrote in message
...
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



  #4   Report Post  
Max
 
Posts: n/a
Default

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)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"PaolaAndrea" wrote in message
...
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



  #5   Report Post  
PaolaAndrea
 
Posts: n/a
Default

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

"Bob Phillips" wrote:

=WEEKDAY(A1,2)5

will be true for Sat or Sun

--
HTH

Bob Phillips

"PaolaAndrea" wrote in message
...
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






  #6   Report Post  
Bob Phillips
 
Posts: n/a
Default

Max!

=OR(WEEKDAY(A$1,2)=6,WEEKDAY(A$1,2)=7)

=WEEKDAY(A$1,2)5

<vbg

--
HTH

Bob Phillips

"Max" wrote in message
...
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)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"PaolaAndrea" wrote in message
...
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





  #7   Report Post  
Bob Phillips
 
Posts: n/a
Default

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

--
HTH

Bob Phillips

"PaolaAndrea" wrote in message
...
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

"Bob Phillips" wrote:

=WEEKDAY(A1,2)5

will be true for Sat or Sun

--
HTH

Bob Phillips

"PaolaAndrea" wrote in message
...
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






  #8   Report Post  
PaolaAndrea
 
Posts: n/a
Default

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
"Max" wrote:

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)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"PaolaAndrea" wrote in message
...
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




  #9   Report Post  
Don Guillett
 
Posts: n/a
Default

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

--
Don Guillett
SalesAid Software

"PaolaAndrea" wrote in message
...
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
"Max" wrote:

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)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"PaolaAndrea" wrote in message
...
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






  #10   Report Post  
Max
 
Posts: n/a
Default

"PaolaAndrea" wrote
Thank you.


You're welcome !

I understand your formula with
the exception of the 2. ..


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"

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----




  #11   Report Post  
Max
 
Posts: n/a
Default

"Bob Phillips" wrote
=OR(WEEKDAY(A$1,2)=6,WEEKDAY(A$1,2)=7)
=WEEKDAY(A$1,2)5
<vbg


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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formating Extreme Question Heather Excel Worksheet Functions 5 May 8th 05 08:06 PM
Expanding conditional formating with reference cells changing CCoop Excel Discussion (Misc queries) 2 May 4th 05 02:36 PM
Conditional Formating Itch Excel Discussion (Misc queries) 1 March 8th 05 07:13 PM
more than 3 conditional formating in excel Manan Excel Discussion (Misc queries) 2 February 7th 05 10:12 PM
Conditional Formating when result is text Lary Excel Worksheet Functions 1 December 16th 04 03:13 AM


All times are GMT +1. The time now is 05:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"