Conditional Format Question when Time crosses over into next day

I'm not sure why you have some of the conditions twice.....but you should be able to accommodate time periods crossing midnight by changing this part

$C9<=E$7,$D9>E$7

to

($C9<=E$7)+($D9>E$7)+($C9>$D9)=2

Barry your the "MAN " yet again.

it work perfectly

Many Thanks

Toonies

Hi Barry,

your formula works great

=AND(LEN($C9)>0,($C9<=E$7)+($D9>E$7+($C9>$D9)=2,SUMPRODUCT(F9,"*"&Task&"*"))>0,($C9)>0,($C9<=E$7)+($D9>E$7+($C9>$D9)=2) - copied down

however I have one small glitch that I cannot overcome

It works fine if C9 and D10 are both filled.

It works fine if C9 is empty and D9 is filled then F9 stays blank (which is what I want)

But if C10 is filled and D10 is empty then F10 is filled (which I don't want)

<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="FONT-SIZE: 20pt"> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-SIZE: 20pt; FONT-WEIGHT: bold">6:00</TD><TD> </TD><TD style="FONT-SIZE: 28pt; FONT-WEIGHT: bold"> </TD><TD style="FONT-SIZE: 28pt; FONT-WEIGHT: bold"> </TD></TR><TR style="HEIGHT: 80px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; FONT-SIZE: 20pt; FONT-WEIGHT: bold">06:00</TD><TD style="FONT-SIZE: 20pt; FONT-WEIGHT: bold"> </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>


If its best to start a new thread then let me know and I will.

Many thanks

Toonies

I have posted this at

http://www.excelforum.com/excel-general/775144-conditional-format-time-formula-need-help-please.html

I have a copy of the file if you wish to look at it just download it.

http://www.mediafire.com/?djr3ubr6wa77nja

I look forward to any suggestions on how to sort my problem

Toonies

Toonies said:

I have a copy of the file if you wish to look at it just download it.

http://www.mediafire.com/?djr3ubr6wa77nja

I look forward to any suggestions on how to sort my problem

Toonies

Click to expand...

I have your file in front of me.

It looks like you're trying to make some sort of Gantt chart?

I can't tell what you're formula is doing just from looking at it.

However, it seems that all you should need to do is add another condition to the AND(...) function:

D9<>""

With that said, 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?

Hi thank you for your rely

Where would you suggest I add the D9<>""

The drop downmenu 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.

heres the coding for reference - well it does the job don't know if it can be refined but I would have to start a new thread to refine it. as there are other sheets in the main file.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Sh.Name
Case Is = "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY", "SUNDAY", "MONDAY"
Set Rng = Worksheets("Times").Range("Task")
Dim rCell As Range

If Union(Target, Range("F9:AO39")).Address = _
Range("F9:AO39").Address Then
Application.EnableEvents = False
For Each rCell In Target
If Len(rCell.Text) < 4 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 28
ElseIf Len(rCell.Text) = 4 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 24
ElseIf Len(rCell.Text) = 5 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 26
ElseIf Len(rCell.Text) = 6 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 17
ElseIf Len(rCell.Text) = 7 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 19
ElseIf Len(rCell.Text) = 8 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 17
ElseIf Len(rCell.Text) = 9 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 17
ElseIf Len(rCell.Text) = 10 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 17
ElseIf Len(rCell.Text) = 11 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 17
ElseIf Len(rCell.Text) = 12 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 13.5
ElseIf Len(rCell.Text) = 13 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 13.5
ElseIf Len(rCell.Text) = 14 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 13.5
ElseIf Len(rCell.Text) = 15 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 12.5
ElseIf Len(rCell.Text) = 16 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 13.5
ElseIf Len(rCell.Text) = 17 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 11.5
ElseIf Len(rCell.Text) = 18 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 13.5
ElseIf Len(rCell.Text) = 19 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 12
ElseIf Len(rCell.Text) = 20 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 12
ElseIf Len(rCell.Text) = 21 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 12
ElseIf Len(rCell.Text) = 22 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 12
ElseIf Len(rCell.Text) = 23 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 11
ElseIf Len(rCell.Text) = 24 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 12
ElseIf Len(rCell.Text) = 25 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 12
ElseIf Len(rCell.Text) = 26 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 11
ElseIf Len(rCell.Text) = 27 Then
rCell.Font.Name = "Arial"
rCell.Font.Size = 11
Else
rCell.Font.Name = "Arial"
rCell.Font.Size = 13.5
End If
Next
Application.EnableEvents = True
End If
End Select
End Sub

Toonies said:

Hi thank you for your rely

Where would you suggest I add the D9<>""

Click to expand...

Well, now that I think about it you can just put it at the very end of the formula like this:

=Formula*(D9<>"")

I'm not much of a programmer so you'll get better help from someone else wrt your code.

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