根据结果比较2列和颜色填充

Question

我有一列的O或C表示打开和关闭。我的另一个专栏有日期。我想将日期与今天的日期进行比较，并使用打开和关闭来帮助确定是否需要更改颜色以标记对它的关注。这是我迄今为止的代码，但它给了我错误。

Sub datee()
Dim DueDate As Date
Dim OpenClosed As Integer
Dim now As Date

DueDate = Range("D5:D8").Value  ----This is the line that I am getting my error on. 
OpenClosed = Range("E5:E8").Value
If OpenClosed = C Then
With Selection.Interior
activecell.Interior.ColorIndex = 0
End With
ElseIf OpenClosed = O Then
If DueDate < now Then
With Selection.Interior
activecell.Interior.ColorIndex = 255
End With
ElseIf OpenClosed = O Then
If DueDate > now Then
With Selection.Interior
activecell.Interior.ColorIndex = 0
End With
End If
End If
End If
End Sub

Answer 1

试试这个代码。我添加了第三种颜色以获得更多选择。这取决于你是否想改变它。

Sub date()
Dim i As Integer
For i = 5 To 8
If Range("E" & i).Value = "C" Then
Range("D" & i).Interior.COLOR = vbGreen
Else
' all of these will be assumed to be "O" or not "C"
If CDate(Range("D" & i).Value) < CDate(Date) Then
Range("D" & i).Interior.COLOR = vbRed
Else
Range("D" & i).Interior.COLOR = vbWhite
End If
End If
Next i
End Sub

我不确定你想给什么细胞上色。所以我假设日期单元格是彩色的。如果你想包括OpenClosed列，只需修改行如下：

Range("D" & I & ":E" & i).Interior.COLOR = vbRed