Conditional Format Question when Time crosses over into next day

[Toonies]

Hi and thanks for taking the time to look at my question which is as follows

I am using the following CF formula

Condition 1 - colours a cell within a certain time frame

=AND(LEN($C9)>0,$C9<=E$7,$D9>E$7,SUMPRODUCT(COUNTIF(F9,"*"Task"*"))>0)+AND(LEN($C9)>0,$C9<=E$7,$D9>E$7)

it works great for time within that day

ie: 12:00 to 19:00

but if the time crosses over into the next day the cells within the time range don't change colour.

ie: 22:00 to 06:00

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 6px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 6px"><COL style="WIDTH: 101px"><COL style="WIDTH: 101px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 53px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 18pt" rowSpan=3>Start</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 18pt; FONT-WEIGHT: bold" rowSpan=3>Finish</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">06:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">06:30</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 11pt">To</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 11pt">To</TD></TR><TR style="HEIGHT: 53px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="TEXT-ALIGN: center; FONT-SIZE: 8pt">06:00</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">06:30</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #99ccff; FONT-SIZE: 20pt; FONT-WEIGHT: bold">07:00</TD></TR><TR style="HEIGHT: 6px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 80px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-SIZE: 15pt"></TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 20pt; FONT-WEIGHT: bold">6:00</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 20pt; FONT-WEIGHT: bold">7:00</TD><TD></TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 28pt; FONT-WEIGHT: bold"></TD><TD style="BACKGROUND-COLOR: #99ccff; FONT-SIZE: 28pt; FONT-WEIGHT: bold"></TD></TR></TBODY></TABLE>

I look forward to any help or replies

OS Windows Vista Excel 2003

Many thanks

Toonies

 

[T. Valko]

Hi thanks for the suggestion however I'm still at a loss

This is the CF formula you have for cell F9:

=AND(LEN($C9)>0,($C9 < =E$7)+($D9 > E$7)+($C9>$D9)=2,SUMPRODUCT(COUNTIF(F9,"*"&Tasks&"*"))>0)+AND(LEN($C9)>0,($C9 < =E$7)+($D9 > E$7)+($C9>$D9)=2)

Just add the bit *($D9<>"") to the end like this:

=AND(LEN($C9)>0,($C9 < =E$7)+($D9 > E$7)+($C9>$D9)=2,SUMPRODUCT(COUNTIF(F9,"*"&Tasks&"*"))>0)+AND(LEN($C9)>0,($C9 < =E$7)+($D9 > E$7)+($C9>$D9)=2)*($D9<>"")

You didn't answer my question about:

I can't figure out what this supposed to be doing:

SUMPRODUCT(COUNTIF(F9,"*"&Tasks&"*"))>0

F9 contains a drop down and the selections are the numbers 1 to 10. Tasks is a named range that contains a blank cell and the numbers 1 to 10. What are the wildcards for?

 

[Toonies]

SUMPRODUCT(COUNTIF(F9,"*"&Tasks&"*"))>0

Hi the drop downmenu with a named range is basically Department No's I'm looking to control the Font size via a VBA throught TEXT string as I will be putting in additional descriptions in the dropdown menu its sort of a work around Warp Text and Shrink to fit.

I have tried your solution however no joy many thanks

 

[T. Valko]

SUMPRODUCT(COUNTIF(F9,"*"&Tasks&"*"))>0

Hi the drop downmenu with a named range is basically Department No's I'm looking to control the Font size via a VBA throught TEXT string as I will be putting in additional descriptions in the dropdown menu its sort of a work around Warp Text and Shrink to fit.

I have tried your solution however no joy many thanks

Ok...

Well, I don't understand what you're trying to do with this so I'll just move on.

Maybe a fresh set of eyes will be of more help.

Good luck!

 

[Toonies]

hi and many thanks for taking the time to look at this

Toonies