将单元格中的日期时间与两个日期 VBA 进行比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8018736/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Comparing DateTime from cell with two dates VBA
提问by Amra
I am trying to compare the dates with time from inside a cell with Now()
我正在尝试使用 Now() 将单元格内的日期与时间进行比较
I have the code so the style sheet autorefresh itself so when first date with time is overdue cell go green and when the second date and time is overdue the cell would go red.
我有代码，所以样式表会自动刷新，所以当第一个日期和时间过期时，单元格变为绿色，当第二个日期和时间过期时，单元格将变为红色。
I can only get this to work comparing the date without the time with DateValue.
我只能让这个工作比较没有时间的日期与DateValue。
There is a colunm with cells with two dates with times mayorly (sometimes there is only 1 date and sometimes there is only one date without time)
有一列带有两个日期和时间的单元格（有时只有 1 个日期，有时只有一个没有时间的日期）
Cells with two dates with time would be as the example below.
具有两个日期和时间的单元格将如下例所示。
-----------------
12/11/2011 09:00
13/11/2011 15:00
-----------------
This is what I have so far after several attempts (cosidering that many of the attemps have been deleted already)
这是我经过多次尝试后到目前为止所拥有的（考虑到许多尝试已经被删除）
Sub Worksheet_Change()
Set aWorkBook = Workbooks("Workbook.xls").Sheets("sheet 2").Range("C3:C10")
For Each Cell In aWorkBook
'MsgBox (Mid(Cell.Value, 1, 19))
If Cell.Value <> "" Then
MsgBox (Now < Mid(Cell.Value, 11, 6))
'MsgBox ((Mid(Cell.Value, 1, 17)) < Now())
'MsgBox ((Cell.Value))
If (CDate(Mid(Cell.Value, 1, 17)) < Now()) Then
'MsgBox ("Hello")
'Cell.Interior.ColorIndex = 3
End If
End If
Next
End Sub
In this case I am using msgbox to test outcome but not success.
在这种情况下，我使用 msgbox 来测试结果但不成功。
Any help would be very much appreciated.
任何帮助将不胜感激。
回答by chris neilsen
Looking at your code it apears the date/time values are strings. If thats the case use
查看您的代码，日期/时间值是字符串。如果是这种情况，请使用
(DateValue(Cell) + TimeValue(Cell)) > Now()
If cells contain values formatted as dates, use
如果单元格包含格式为日期的值，请使用
Cell > Now()
You might be better off using Conditional Formatting rather than the _Change event. Eg to format cell A3use conditional formula (note, no $'s)
您最好使用条件格式而不是 _Change 事件。例如格式化单元格A3使用条件公式（注意，没有$）
(DateValue(A3) + TimeValue(A3)) > Now()
then copy paste formats to any other cells you want
然后将粘贴格式复制到您想要的任何其他单元格
