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=540438

Try below:
Step 1, Input formula on cell z42:
=IF(AND((B42<""),(G42="")),"*","")
Step 2, copy the formula to Z43, Z44, Z45
Go to A1, add formula
=IF(OR((z42="*"),(z43="*"),(z44="*"),(z45="*")),"* ","")

After completing this steps, you would find if person 'in' the hospital, A1
will show
a *. You may change the * to Person In. If you still need the cell change
color, you may require to run a macro.



"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=540438