+ Reply to Thread
Results 1 to 4 of 4

Format cell color based on multiple cell values

  1. #1
    Registered User
    Join Date
    04-24-2006
    Location
    Nevada
    Posts
    16

    Smile Format cell color based on multiple cell values

    I want the background color for cell A1 to be purple (13) if any of the below statements are true:

    B42>"" and G42="" or
    B43>"" and G43="" or
    B44>"" and G44="" or
    B45>"" and G45=""

    Or you could look at it this way:

    B42 NOT ISBLANK and G42 ISBLANK or
    B43 NOT ISBLANK and G43 ISBLANK or
    B44 NOT ISBLANK and G44 ISBLANK or
    B45 NOT ISBLANK and G45 ISBLANK or

    Conditional formatting will only allow three conditions. Is there code I can use in the worksheet event to get this to work?

    B42, B43, B44, B45 are all fields called Hospital Admit Dates. G42, G43, G44, G45, are all Discharge Dates. A person can have several hospital admissions so I've made room for 4 admit dates and 4 corresponding discharge dates. If the person is in the hospital, I want cell A1 to be purple.

    If any of the B cells contain a date and the corresponding G cell does not contain a date, the person is in the hospital because there's no discharge date in the corresponding G cell but there is a date in the admit B cell.

    My brain is stuck in a vicious looping cycle so I can't think straight and don't know if this is possible.
    Zenaida

  2. #2
    Elkar
    Guest

    RE: Format cell color based on multiple cell values

    Actually, Conditional Formatting is limited to 3 formats, not 3 conditions.
    You can have several conditions if you use the AND and OR functions. Try
    this for your formula:

    =OR(AND(B42<>"",G42=""),AND(B43<>"",G43=""),AND(B44<>"",G44=""),AND(B45<>"",G45=""))

    HTH,
    Elkar


    "Zenaida" wrote:

    >
    > I want the background color for cell A1 to be purple (13) if any of the
    > below statements are true:
    >
    > B42>"" and G42="" or
    > B43>"" and G43="" or
    > B44>"" and G44="" or
    > B45>"" and G45=""
    >
    > Or you could look at it this way:
    >
    > B42 NOT ISBLANK and G42 ISBLANK or
    > B43 NOT ISBLANK and G43 ISBLANK or
    > B44 NOT ISBLANK and G44 ISBLANK or
    > B45 NOT ISBLANK and G45 ISBLANK or
    >
    > Conditional formatting will only allow three conditions. Is there code
    > I can use in the worksheet event to get this to work?
    >
    > B42, B43, B44, B45 are all fields called Hospital Admit Dates. G42,
    > G43, G44, G45, are all Discharge Dates. A person can have several
    > hospital admissions so I've made room for 4 admit dates and 4
    > corresponding discharge dates. If the person is in the hospital, I
    > want cell A1 to be purple.
    >
    > If any of the B cells contain a date and the corresponding G cell does
    > not contain a date, the person is in the hospital because there's no
    > discharge date in the corresponding G cell but there is a date in the
    > admit B cell.
    >
    > My brain is stuck in a vicious looping cycle so I can't think straight
    > and don't know if this is possible.
    >
    >
    > --
    > Zenaida
    >
    >
    > ------------------------------------------------------------------------
    > Zenaida's Profile: http://www.excelforum.com/member.php...o&userid=33802
    > View this thread: http://www.excelforum.com/showthread...hreadid=540437
    >
    >


  3. #3
    Registered User
    Join Date
    04-24-2006
    Location
    Nevada
    Posts
    16
    I'm using Excel 2002 and am not great at fixing syntax errors. I've tried several things but when I use your code in the conditional formatting it tells me the formula contains an error. Do you know what I need to maybe add or remove?

    =OR(AND(B42<>"",G42=""),AND(B43<>"",G43=""),AND(B44<>"",G44=""),AND(B45<>"",G45=""))
    Last edited by Zenaida; 05-09-2006 at 06:54 PM.

  4. #4
    Registered User
    Join Date
    04-24-2006
    Location
    Nevada
    Posts
    16
    Sorry, my mistake. There was a space in the cell reference B44. Your code works perfect. Thanks so much!!

+ 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