Hi all
OS Vista - Excel 2003
I have the following problem which is as follows
I Have this CF formula in cells F9 to AO38 copied across and down
Every thing works fine with 1 exception
=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)
It works fine if C9 and D10 are both filled with Time it colours the result cells within the specified time range (This is correct)
It works fine if C9 is empty and D9 is filled with Time the result cells stay blank
(which is what I want)
But if C9 is filled with Time and D9 is empty it fills the Cells from when the specified Time starts right to the end of the cell range (which I don't want)
What I need is for the resulting cells to remain blank if you get my drift.
I have attached an example file if you wish to view it it does contain a Macro for the Time cells.
Macro works 100% for Time cells
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStr As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("C9:C39", "D9:D38")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "Are you sure you want to enter this"
Application.EnableEvents = True
End Sub
I also have details posted at
http://www.mrexcel.com/forum/showthread.php?t=548036
Many thanks
Toonies
Bookmarks