VBA If/Then/Else formatting problem

[ackackack]

Hello! I am pretty new to VBA, and I am not sure why my code is not working.

I wanted to create a subroutine that changed the interior color of a range of cells to light red if the date in the cell is more than 90 older than TODAY() (to show which events are past-due), unless the cell is already filled in green (which means the event is paid for) or if the cell is blank (which is read as having a value of 0).

Using VBA, I customized the colors so that the green I am using is ColorIndex 10, and the light red I am using is ColorIndex 5. I couldn't seem to get VBA to recognize the TODAY() function, so I had it reference a cell ("R4") which displays a date that is 90 days before TODAY().

Here is the code I created to show past-due events:

Sub PastDue()

Dim Value As Date
Dim cell As Range
For Each cell In Range("C19:AK38")

If cell.Interior.Color = 10 Then
Exit Sub

ElseIf cell.Value = 0 Then
Exit Sub

ElseIf cell.Value < "R4" Then
        cell.Interior.ColorIndex = 5

End If
Next
End Sub

When I run this subroutine, it only affects the first row in the range, Row 19. Also, it is not listening to the portion of the code that tells it to stop if a cell is already green, and it changes already-green cells light red. It also doesn't seem to understand Dates as Values and is turning any date light red, even the ones that are greater than "R4".

So, there are a bunch of things wrong with this code! While I feel like I am very close to getting it to do what I want, I am not sure how to fix it. Any help is very much appreciated!!

Thanks!!

 

[CodeNinja]

Try something like the following:

Sub pastDue()
Dim cl As Range
For Each cl In Range("C19:AK38")
    If cl.Interior.ColorIndex <> 10 And cl.Value <> 0 And cl.Value < Now() - 90 Then
        cl.Interior.ColorIndex = 5
    End If
Next cl

End Sub

Good luck,

CN

 

[ackackack]

That worked BEAUTIFULLY! Thank you so so much!