VBA Colour Code Cell if X Days Greater than Today

[SiMoore]

Hoping for some assistance, in brief I'm trying to apply a conditional format to a column based on a a number of days, the first condition below will highlight the cell if the date is less than today's date which works perfectly. The next condition I would like it to change the colour of the cell to amber only if the date is between today and 90 days and then a final condition would be to colour the cell green if it is beyond 90 days.

I've tried every variation going and searched high-and-low but can't quite figure it out.

Thanks in advance.

'Support End Date (Less Than Today)
With ThisWorkbook.Worksheets("Output").Range("H2:H1000").FormatConditions
.Add Type:=xlExpression, Formula1:="=AND(H2<>"""",H2<TODAY())"
With .Item(.Count)
.Interior.Color = 255
.SetFirstPriority
End With

'Support End Date (Between Today and 90 Days)
With ThisWorkbook.Worksheets("Output").Range("H2:H1000").FormatConditions
.Add Type:=xlExpression, Formula1:="=AND(H2<>"""",H2>TODAY())"
With .Item(.Count)
.Interior.Color = RGB(255, 102, 0)
.SetFirstPriority
End With

'Support End Date (91 Days Onwards)
With ThisWorkbook.Worksheets("Output").Range("H2:H1000").FormatConditions
.Add Type:=xlExpression, Formula1:="=AND(H2<>"""",H2>TODAY())"
With .Item(.Count)
.Interior.Color = RGB(153, 204, 0)
.SetFirstPriority
End With

 

[6StringJazzer]

Please use code tags when posting code. See underlined portions. Underlining did not seem to survive submittal. BTW why are you doing this in code if you are hardcoding all the ranges? Why not just implement it directly in the sheet?

    'Support End Date (Less Than Today)
    With ThisWorkbook.Worksheets("Output").Range("H2:H1000").FormatConditions
        .Add Type:=xlExpression, Formula1:="=AND(H2<>"""",H2<TODAY())"
        With .Item(.Count)
            .Interior.Color = 255
            .SetFirstPriority
    End With

   'Support End Date (Between Today and 90 Days)
    With ThisWorkbook.Worksheets("Output").Range("H2:H1000").FormatConditions
        .Add Type:=xlExpression, Formula1:="=AND(H2<>"""",H2>TODAY(),H2<=TODAY()+90)"
        With .Item(.Count)
            .Interior.Color = RGB(255, 102, 0)
            .SetFirstPriority
    End With

  'Support End Date (91 Days Onwards)
    With ThisWorkbook.Worksheets("Output").Range("H2:H1000").FormatConditions
        .Add Type:=xlExpression, Formula1:="=AND(H2<>"""",H2>TODAY()+90)"
        With .Item(.Count)
            .Interior.Color = RGB(153, 204, 0)
            .SetFirstPriority
    End With

 

[SiMoore]

That's fantastic thank you so much. Sorry, bit of a novice to all this so just fumbling my way through via Google, message boards and YouTube so as you state above "Why haven't I implemented this directly into the sheet" - the sheet is created on the fly, with the above it's pretty much complete and yes there probably is a better, more refined way to produce the same overall output but it works so mission accomplished.

Thanks again

 

[SiMoore]

Sorry one final question - just need to add in one final condition that colour codes based on between 180-365 days. Above provides a colour code for anything beyond 90 days and i've tried the below but this now simply colour codes everything less than 365 days as green so overwrites any previous condition.

'Support End Date (Between 180-365 Days)
With ThisWorkbook.Worksheets("Output").Range("H2:H1000").FormatConditions
.Add Type:=xlExpression, Formula1:="=AND(H2<>"""",H2<TODAY()+365)"
With .Item(.Count)
.Interior.Color = RGB(153, 204, 0)
.SetFirstPriority
End With

 

[SiMoore]

Managed to figure it out:

'Support End Date (Within 180-365 Days)
With ThisWorkbook.Worksheets("Output").Range("H2:H1000").FormatConditions
.Add Type:=xlExpression, Formula1:="=AND(H2<>"""",H2>TODAY()+180,H2<=TODAY()+365)"
With .Item(.Count)
.Interior.Color = RGB(153, 204, 0)
.SetFirstPriority
End With