Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm looking for a way to highlight a range of cells in a row based on the time that is within the cell. And I'd like to have a way to input the time and have it highlight anything that is greater than the time entered. The cell contains the date-time in the m/d/yyy hh:mm:ss AM/PM format. Example: 3/27/2009 7:00:00 PM

    Here is what I've tried with no success (it doesn't crash...just doesn't work):

    Sub Time()
    Dim MyInput
    MyInput = InputBox("Enter time")
    For Each cell In ActiveSheet.Range("O2:O450").Cells.SpecialCells(xl CellTypeConstants)
    If cell.Value > MyInput Then
    Range("A" & cell.Row & ":X" & cell.Row).Interior.Color = RGB(146, 208, 80)
    End If
    Next cell
    End Sub

    I can get it to highlight based on an exact time by using Like.

    If cell.Value Like "*" & MyInput & "*" Then

    Is there a way to do what I'm wanting to do?

    I'm using Excel 2007.

    Thanks in advance for the pointers.

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    You could just use conditional formatting.

    I'm not sure how to do this in Excel 2007, in Excel 2003 you would
    • Select all the cells that you want to apply the formatting to
    • Select Conditional Formatting from the Format menu
    • Select Cell Value is Greater than in the dropdown boxes
    • Select the cell with the date you want to compare to in the next box
    • Set your formatting
    See attached graphic...

    [attachment=83167:Conditio...rmatting.GIF]
    Attached Images Attached Images

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use conditional formatting - you wouldn't need any code.
    Let's say that you enter the date and time to compare with in cell Z1.

    Select columns A:X, or the range that you want to format conditionally.
    Note which cell is the active cell within the selection. For illustration purposes, I'll assume it is cell A2.

    Activate the Home tab of the Ribbon.
    Click Conditional Formatting in the Styles section.
    Click Manage Rules.
    Click New Rule...
    Select "Use a formula to determine which cells to format".
    Enter the following formula:

    =$O2>$Z$1

    If the active cell had been in row 4, you'd have used

    =$O4>$Z$1

    (Remember, Z1 is the cell with the date/time to compare column O with)
    Click Format...
    Activate the Fill tab.
    Select the highlight color you want.
    Click OK to close the Format Cells dialog.
    Click OK to close the Conditional Formatting dialog.

    If you change the value in Z1, the highlighting will be adjusted automatically.

  4. #4
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the responses. I've tried conditional formatting and it won't do what I'm trying to do. The cell contains both the date and time and I'm looking have it highlight based on the time only. In my format example above, let's say I want it to highlight anything after 6:00PM. The example above would need to be highlighted regardless of the date. The spreadsheet is updated with new data daily and the time that needs highlighted will not always be the same.

    I've tried entering the time into an inputbox and then concatenating it with a Today() formula and I can't get that to work either. Copying and pasting the result as values gives me a number value that does not correspond with the correct date.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can adapt the formula for conditional formatting from my previous reply to look at the time only and to ignore the date: change

    =$O2>$Z$1


    to

    =$O2-INT($O2)>$Z$1

    The -INT($O2) part subtracts the date from the date+time in O2, leaving only the time.

  6. #6
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello again.

    Thank you for trying to help me. It is much appreciated. I do not think conditional formatting will work. There will be some instances where I do not need a row colored even though it meets the conditions. The conditional formatting overwrites all formatting. I will keep digging around to see what I can come up with.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you state the exact conditions under which a row should be highlighted, it may be possible to use either conditional formatting or VBA code.

    However, if there's no logic to the decision, it'll be hard to "automate" it.

    (Conditional formatting has the advantage that it does not permanently change the cell formatting - if the value doesn't meet the criteria, the row will show the original formatting. If you use code to highlight a row, this will replace the original formatting)

Posting Permissions

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