Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Formating Depending On If Statement (2000)

    Row A1:AD1 contains days of the week in the format "dd-mmmm-yyyy" (i. e., "03-July-2002"). Row A2:AD2 contains the corresponding day of the week of the date in the cell immediately above the weekday cell (i. e., cell A2 contains "Monday", inasmuch as cell A1 contains "01-July-2002"). How can I cause the (1) the formatting in cell A3 to be (a) shaded, or ([img]/forums/images/smilies/cool.gif[/img] contain a certain type of border (i. e., an "X"), or, (2) the contents of cell A3 to return "N/A", when cell A2 is a Saturday or Sunday?
    Thanks,
    Jeff

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formating Depending On If Statement (2000)

    You can use conditional formatting for (1) (a) or ([img]/forums/images/smilies/cool.gif[/img].
    Select cell A3.
    Select Format/Conditional Formatting...
    In the first box, select Formula
    In the second box, type =OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7)
    Click the Format... button
    Set the formatting options you like (Font, Border, Shading)
    Click OK twice.
    Fill right to AD3.

    For (2), use a formula
    =IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7),"N/A","")

  3. #3
    Lounger
    Join Date
    Oct 2001
    Location
    Isleworth, Gtr London, England
    Posts
    28
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Formating Depending On If Statement (2000)

    Hi Jeff,

    select A3:AD3 and do menu command Format/Conditional Formatting.

    In Condition 1 box select Formula Is, and in the next box type ...
    =MOD(WEEKDAY(A1),6)=1
    and click the Format... button to choose a pattern for shading Sats and Suns. Press OK, press OK.

    That should do it.

    Glenn.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formating Depending On If Statement (2000)

    I still can't get an "X" pattern of borders (i. e., 2 crossing lines-one from the top left corner of a cell to the bottom right corner, and the second from the top right corner of a cell to the bottom left corner) with any of the suggested solutions! What am I doing wrong?
    Thanks,
    Jeff

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formating Depending On If Statement (2000)

    I hadn't noticed it before, but now that you point it out: the diagonal border options are greyed out in the format dialog for conditional formatting. I guess Microsoft hasn't implemented this in 97/2000 (perhaps in XP?) As an alternative, you can use shading, with a solid color or a pattern.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Formating Depending On If Statement (2000)

    Nope, still greyed out in XP.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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