+ Reply to Thread
Results 1 to 7 of 7

Conditional Format Time Formula

  1. #1
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    503

    Conditional Format Time Formula

    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
    Attached Files Attached Files
    Last edited by Toonies; 05-07-2011 at 04:00 PM. Reason: OS Vista - Excel 2003

  2. #2
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    503

    Re: Conditional Format Time Formula - Need Help Please

    Hi just a quick Bump!

  3. #3
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Conditional Format Time Formula - Need Help Please

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

  4. #4
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    503

    Re: Conditional Format Time Formula - Need Help Please

    Hi many thanks for the info about correct posting

    here is the formula

    Please Login or Register  to view this content.
    however I found the fix that I was after which is

    Please Login or Register  to view this content.
    This has thrown up another issue which is when the time crosses over into the next day say

    22:00 to 7:00 it fills the cells of that time range but also fills the cells of 6:00 to 7:00 of the previous day.

    is there a way to stop that of do I have to start a new post

  5. #5
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Conditional Format Time Formula - Need Help Please

    Code tags are used to wrap formulas AND VBA code . Please correct your Original post - Thx

  6. #6
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    503

    Re: Conditional Format Time Formula - Need Help Please

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor Toonies's Avatar
    Join Date
    07-30-2009
    Location
    Newcastle, UK
    MS-Off Ver
    Excel 2016
    Posts
    503

    Re: Conditional Format Time Formula

    I am pretty sure that the problem is due to this part of the code

    Please Login or Register  to view this content.
    is there any way to change it to meet my needs

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1