Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136

    Unanswered: Change cell color based on values of multiple cells

    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 an admit date and the corresponding G cell discharge date does not contain a date, the person is in the hospital and I want cell A1 to be purple.

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

  2. #2
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Someone helped me. I placed the following code in the conditional formatting for cell A1 and it works great:

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

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    It also helps to set your sheet to use RC notation when doing conditional formatting.

  4. #4
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Thanks for your reply. I'll do some research on what RC notation is.

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by Zenaida
    Thanks for your reply. I'll do some research on what RC notation is.
    Well, the first place to start is by turning it on (Tools / Options / General / check R1C1 reference style).

    It does the same thing as A1 notation, but it can be a little clearer when you aren't sure of the origin cell (as when using conditional formatting on a range.)

    Here's the difference:

    Absolute: $A$1:$D$10 vs. R1C1:R10C4

    Relative (assuming you're at E20): A110 vs. R[-19]C[-4]:R[-10]C[-1]

    Here's why I use it:

    Say I've column 3 is Status. I want every cell in rows with status "Bad" to have a red background.

    So I set the formula for my conditional formatting to be =RC3="Bad". With A1 style formatting I have to guess which cell Excel thinks I'm in to do relative addressing.

  6. #6
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    That's awesome! Thanks for the info.

Posting Permissions

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