Highlight cells based on input

Topic

New Reply

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(xlCellTypeConstants)
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.

Reply | Quote

Replies

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…

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote