VBA If/Then/Else formatting problem


New Member
Aug 21, 2015
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
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!!



Well-known Member
Feb 18, 2013
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,


Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...