Conditional Format Question

[Toonies]

Hi and thank you for looking.

I have the following question.


I am using the following CF Formula

=AND($E10<=H$6,$F10>G$8)+SUMPRODUCT(COUNTIF(H10,"*"&Task&"*"))>0

which changes the color of the cell

This works fine for when time is both entered into E10 & F10

It works fine when Time is entered in E10 but not in F10 (the cells don't change color, which is exactly what is required.)

However if Time is put in F10 but not into E10 it changes the cell color which is not what I am looking for. I would prefer that the cells to stay blank

Hope I have explained this ok



<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 33px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-WEIGHT: bold" rowSpan=3 colSpan=2>SHIFT</TD><TD style="COLOR: #ffffff; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 8pt; FONT-WEIGHT: bold">06:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 8pt; FONT-WEIGHT: bold">06:30</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 8pt; FONT-WEIGHT: bold">|</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 8pt; FONT-WEIGHT: bold">|</TD></TR><TR style="HEIGHT: 34px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center; COLOR: #ffffff; FONT-SIZE: 8pt; FONT-WEIGHT: bold">06:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 8pt; FONT-WEIGHT: bold">06:30</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 8pt; FONT-WEIGHT: bold">07:00</TD></TR><TR style="HEIGHT: 6px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="FONT-SIZE: 8pt; FONT-WEIGHT: bold"> </TD><TD style="FONT-SIZE: 8pt; FONT-WEIGHT: bold"> </TD></TR><TR style="HEIGHT: 37px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center">6:00</TD><TD style="TEXT-ALIGN: center">7:00</TD><TD> </TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 7pt"> </TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 7pt"> </TD></TR><TR style="HEIGHT: 37px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center">6:00</TD><TD> </TD><TD> </TD><TD style="FONT-SIZE: 7pt"> </TD><TD style="FONT-SIZE: 7pt"> </TD></TR><TR style="HEIGHT: 37px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD> </TD><TD style="TEXT-ALIGN: center">7:00</TD><TD> </TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 7pt"> </TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 7pt"> </TD></TR></TBODY></TABLE>

Excel tables to the web >> Excel Jeanie HTML 4


I look forward to any replies

 

[Andrew Poulsom]

Maybe try:

=AND(LEN($E10)>0,$E10<=H$6,$F10>G$8)+SUMPRODUCT(COUNTIF(H10,"*"&Task&"*"))>0

 

[Toonies]

Many Thanks for your quick reply and it works like a charm.