StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow do I search a column for any cells with dates earlier than a specific date and highlight them?
text
Body
copied!<p>I'm simply trying to search a specific column for any dates earlier than what the user specifies.</p> <pre><code>Dim rCell As Range Dim TheAnswer$ TheAnswer = InputBox("In M/D/YYYY format, enter the first day of the month for which this report is being run." & _ vbCr & vbCr & "For example, you would enter ""12/1/2012"" for the December 2012 report.", "Enter Date M/D/YYYY") For Each rCell In ActiveSheet.Range("D:D").Cells If rCell.Value < TheAnswer Then rCell.Interior.Color = RGB(255, 102, 0) End If Next rCell </code></pre> <p>My problem is that this doesn't always pick the right ones. If I use a month or day with two digits, it completely ignores those months and days with one digit. I already formatted the cells with the 03/14/01 date format, so they display fine, but the value doesn't match. Could I simply change what displays to match the value? If so, how do I do that?</p> <p>Thanks in advance.</p> <p><em><strong>Update: With help from Kevin, I was able to solve this. In case anyone finds it useful, here's my finalized code:</em></strong></p> <pre><code>Range(Cells(2, 4), Cells(Rows.Count, 4).End(xlUp)).Select Dim rCell As Range Dim TheAnswer$ Dim ConvertedDate# TheAnswer = InputBox("In M/D/YY format, enter the first day of the month for which this report is being run." & _ vbCr & vbCr & "For example, you would enter ""12/1/2012"" for the December 2012 report.", "Enter Date M/D/YY") ConvertedDate = CDate(TheAnswer) For Each rCell In Selection If rCell.Value <> "" Then If rCell.Value < ConvertedDate Then rCell.Interior.Color = RGB(255, 102, 0) End If End If Next rCell </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload