Change color of cell using VBA FOR Loop

[ndjustin20]

Hello,

I am trying to change the color of a cell, in a certain column, if the cells date is within 60 days of the date in the cell using VBA. I have figured out a way to do this using conditional formating though the problem I am running into is then filtering that specific color of cell as VBA doesn't recognize the conditional format. Here is what I have for a loop but it isn't working...

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

Any and all help is greatly appreciated


Justin

 

[kpark91]

Just to confirm, when you're comparing dataColE - Now() <= 60.
You're seeing if they are less than 60 days apart.
It could be that orrrr

you could use this method

Dim i%
For i = 7 To 125
    If Range("E" & i).Value - Now() <= 60 Then
        Range("E" & i).Interior.ColorIndex = 3
    End If
Next i

using loop like this is usually faster than For Each loops.

and make sure your column E7:E125 are formatted as time. (or... date..I dunno lol)

 

[ndjustin20]

I am getting a type mismatch error using the following code...

Sub findWithin60Days()
Dim i%
For i = 7 To 125
    If Range("E" & i).Value - Now() <= 60 Then
        Range("E" & i).Interior.ColorIndex = 3
    End If
Next i
End Sub

 

[kpark91]

What are you trying to subtract against?
Now() returns the date and the time fyi.

So, what is in your E7:E125?

 

[ndjustin20]

Dates are in Column E. So I am subtracting the date in column E from todays date to see if it is <= 60 days so I know if I need to renew that account which works though I can't figure out how to make it run in VB code. I can get the following to run using conditional formatting...


=AND(A1<>"",A1-TODAY()<=30)

 

[kpark91]

Instad of using Now()
try using Date()

 

[ndjustin20]

I get the same type mismatch error..

 

[kpark91]

Try

Sub findWithin60Days()
Dim i%
For i = 7 To 125
    If Range("E" & i).Value <> "" Then
        If Range("E" & i).Value - Date() <= 60 Then
            Range("E" & i).Interior.ColorIndex = 3
        End If
    End If
Next i
End Sub

 

[ndjustin20]

Below is the line of code that is throwing the type mismatch error "13"

Sub findWithin60Days()
Dim i%
For i = 7 To 125
    If Range("E" & i).Value <> "" Then
   [COLOR=red] If Range("E" & i).Value - Date() <= 60 Then
[/COLOR]        Range("E" & i).Interior.ColorIndex = 3
    End If
    End If
Next i
End Sub

 

[kpark91]

Please tell me what your E7:E125 cell format is in.

And an example of how they look like! (ex. 2/5/2011, mm/dd/yyyy)