Loop through a column of dates

[ndjustin20]

I am looking at building a loop to loop through say column A for dates that are NOW()-30 and then change the fill color of the cell it finds to a color. I'm not sure what would be the best possible solution for soemthing like this and any help would be much appreciated.

Either VBA or an Excel function would work and I'm looking for the most efficient way of doing this.


Justin

 

[VoG]

Try Conditional Formatting with the CF formula

=A1=TODAY()-30

 

[ndjustin20]

I tried that though it highlighted all cells .......I put in …
$E<=TODAY-60 and that will turn the cell yellow..


Ok wait I need a 60. Day range so would need to be both < and > I suppose

 

[VoG]

Maybe

=AND(TODAY()-E3<=60,E3-TODAY()<=60)

 

[ndjustin20]

I can't get the conditional formatting to work so I am gonna try using a loop I think...soemthing like this maybe

Sub seeIfDateIsWithin60Days()
Dim date As Range
For Each date In Range("E7:E125")
If date < TODAY() - 60 And date > TODAY - 60 Then
NEED TO FIGURE OUT HOW TO CHANGE THE FILL COLOR HERE SOMEHOW
End If
Next quantity
End Sub

 

[VoG]

Maybe

Sub seeIfDateIsWithin60Days()
  Dim d as date,dt As Range
    For Each dt In Range("E7:E125")
        If dt < date - 60 And date > dt - date - 60 Then
        dt.colorindex=3
        End If
    Next d
    End Sub

 

[ndjustin20]

I got this to run but isn't doing anything


Sub seeIfDateIsWithin60Days()
Dim dateColE As Range
For Each dateColE In Range("E7:E125")
If dateColE < Now() - 60 And dateColE > Now() - 60 Then
dateColE.Interior.ColorIndex = 8
End If
Next dateColE
End Sub

 

[ndjustin20]

I tried this....


Sub seeIfDateIsWithin60Days()
Dim d as date,dt As Range
For Each dt In Range("E7:E125")
If dt < date - 60 And date > dt - date - 60 Then
dt.colorindex=3
End If
Next d
End Sub


And i get a type mismatch

 

[ndjustin20]

So I went back to conditional formatting and used this and it worked...


=AND(E1<>"",E1-TODAY()<=60)


I found it on the internet and still don't really understand why it continues to loop through column E when I only put in E1 but it works and that's what is important


Thank you for all your help it's much appreciated.

 

[ndjustin20]

Ok so I still have the same problem though. I am using VBA code to filter the cells that have a Interior.IndexColor change though using conditional formatting doesn't actually change the color so it's reconizable by VBA code. If you click on the cell it doesn't show any formatting even though the conditional formatting changed the color.