CODEWITHSUNDEEP
excelvba
sorg0017
sorg0017

Reputation: 3

Comparing 2 columns and color filling based on results

I have one column that has an O or C for opened and closed. My other column has dates. I want to compare the dates with todays date and use the open and closed to help determine if the color needs to be changed to flag attention to it. Here is the code I have so far but it is giving me errors.

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

Upvotes: 0

Views: 47

Answers (1)

Wils Mils
Wils Mils

Reputation: 633

Try this code. I added a third color for more options. It is up to you if you want to change it.

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

I am not sure what cells you want to color. So I assumed that the date cells will be colored. If you want to include the OpenClosed column, just modify the lines like so:

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

Upvotes: 1

Related Questions