+ Reply to Thread
Results 1 to 11 of 11

Conditional Formating

  1. #1
    PaolaAndrea
    Guest

    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. #2
    Bob Phillips
    Guest

    Re: Conditional Formating

    =WEEKDAY(A1,2)>5

    will be true for Sat or Sun

    --
    HTH

    Bob Phillips

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




  3. #3
    Don Guillett
    Guest

    Re: Conditional Formating

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

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "PaolaAndrea" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #4
    Max
    Guest

    Re: Conditional Formating

    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 <at>yahoo<dot>com
    ----
    "PaolaAndrea" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #5
    PaolaAndrea
    Guest

    Re: Conditional Formating

    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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #6
    Bob Phillips
    Guest

    Re: Conditional Formating

    Max!

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

    =WEEKDAY(A$1,2)>5

    <vbg>

    --
    HTH

    Bob Phillips

    "Max" <[email protected]> wrote in message
    news:[email protected]...
    > 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 <at>yahoo<dot>com
    > ----
    > "PaolaAndrea" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #7
    Bob Phillips
    Guest

    Re: Conditional Formating

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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. #8
    PaolaAndrea
    Guest

    Re: Conditional Formating

    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 <at>yahoo<dot>com
    > ----
    > "PaolaAndrea" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #9
    Don Guillett
    Guest

    Re: Conditional Formating

    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
    [email protected]
    "PaolaAndrea" <[email protected]> wrote in message
    news:[email protected]...
    > 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 <at>yahoo<dot>com
    > > ----
    > > "PaolaAndrea" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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. #10
    Max
    Guest

    Re: Conditional Formating

    "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 <at>yahoo<dot>com
    ----



  11. #11
    Max
    Guest

    Re: Conditional Formating

    "Bob Phillips" <[email protected]> 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 <at>yahoo<dot>com
    ----



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1