fixing code colored cells based on multiple condtiton

  • hi, actually my code doesn't work at all i need help to fix it

    The date in the cell. I want the cell to keep its color green if the cell’s date is 6 months and yellow months less than today's date and red if it is one month after the date of the day and it remains in the cell next to it. If the Done planted color the color disappears and the cell returns white without coloring

    Code
    1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    2. Dim lr As Integer, i As Integer
    3. lr = Cells(Rows.Count, "e").End(xlUp).Row
    4. For i = 2 To lr
    5. If Cells(i, 5) < Month(Date) - 6 Then Cells(i, 5).Interior.Color = vbGreen
    6. If Cells(i, 5) = Date Then Cells(i, 5).Interior.Color = vbYellow
    7. If Month(CDate(Cells(i, 5))) > Month(Date) + 1 Then Cells(i, 5).Interior.Color = vbRed
    8. If Cells(i, 4) = "DONE" Then Cells(i, 4).Interior.Color = vbWhite
    9. Next i
    10. End Sub

    Files

    • colored‬.xlsm

      (14.73 kB, downloaded 77 times, last: )
  • If Cells(i, 5) < Month(Date) - 6 Then Cells(i, 5).Interior.Color = vbGreen

    This will never happen because for today the formula evaluates to if 44012 < 4 -6 then .... ;)


    If Cells(i, 5) = Date Then Cells(i, 5).Interior.Color = vbYellow

    this will change the color if the date is today


    If Month(CDate(Cells(i, 5))) > Month(Date) + 1 Then Cells(i, 5).Interior.Color = vbRed

    will change color only between January and April each year


    If Cells(i, 4) = "DONE" Then Cells(i, 4).Interior.Color = vbWhite

    will never happen because your loop (i) starts with 2


    cheers

  • I could not under stand your request on coloring


    is it

    green: date is 6 month in the future compared to today

    yellow: date is between 6 month in the future and one month in past compared to today

    re: more than one month in the past?