Change color of cell using VBA FOR Loop

ndjustin20

ndjustin20

Board Regular
Joined
May 25, 2011
Messages
69
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...

Code: 
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
 
Sort by date Sort by votes
OMG!!!!!!!!!!!!!!!!!!!!! I'm such an idiot LOL It's because some of the values aren't dates...GEEEEZZZZZZZZZZZ :laugh: Ok so I need to check for date first then run the code I would imagine right....

Code: 
<TABLE style="WIDTH: 92pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=122><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" span=2 width=61><TBODY><TR style="HEIGHT: 17.25pt" height=23><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; WIDTH: 46pt; HEIGHT: 17.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87 height=23 width=61>[FONT=Arial Narrow]NA[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; WIDTH: 46pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89 width=61>[FONT=Arial Narrow]NA[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88 height=22>[FONT=Arial Narrow]NA[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]NA[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88 height=22>[FONT=Arial Narrow]NA[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]NA[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88 height=22>[FONT=Arial Narrow]05/01/11[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]05/31/13[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88 height=22>[FONT=Arial Narrow]06/01/11[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]05/31/13[/FONT]</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 18.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88 height=25>[FONT=Arial Narrow]06/01/11[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]05/31/13[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90 height=22>[FONT=Arial Narrow]03/01/11[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]02/29/12[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88 height=22>[FONT=Arial Narrow]03/01/10[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]02/01/12[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88 height=22>[FONT=Arial Narrow]04/01/10[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]Closed[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88 height=22>[FONT=Arial Narrow]NA[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]NA[/FONT]</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 18.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88 height=25>[FONT=Arial Narrow]08/01/11[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]07/31/13[/FONT]</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 18.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88 height=25>[FONT=Arial Narrow]10/01/10[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]12/31/11[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90 height=22>[FONT=Arial Narrow]05/01/11[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]04/30/13[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90 height=22>[FONT=Arial Narrow] [/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow] [/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90 height=22>[FONT=Arial Narrow]07/01/10[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]12/31/11[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88 height=22>[FONT=Arial Narrow]11/01/10[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow] [/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90 height=22>[FONT=Arial Narrow]01/01/11[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]12/31/11[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90 height=22>[FONT=Arial Narrow]06/01/10[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]01/01/12[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90 height=22>[FONT=Arial Narrow]03/01/11[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]02/28/13[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90 height=22>[FONT=Arial Narrow]NA[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]NA[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90 height=22>[FONT=Arial Narrow]NA[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]NA[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90 height=22>[FONT=Arial Narrow]NA[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]NA[/FONT]</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #e6b9b8; HEIGHT: 16.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90 height=22>[FONT=Arial Narrow]NA[/FONT]</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89>[FONT=Arial Narrow]NA[/FONT]</TD></TR></TBODY></TABLE>
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Beign lazy xD
Code: 
Sub findWithin60Days()
Dim i%
For i = 7 To 125
        On Error Resume Next
        If Range("E" & i).Value - Date() <= 60 Then
            Range("E" & i).Interior.ColorIndex = 3
        End If
Next i
End Sub
 
Upvote 0
here is what I am going with I think..


Sub findWithin60Days()
Dim i%
For i = 12 To 123
If IsDate(Range("E" & i).Value) Then
If Range("E" & i).Value <> "" Then
If Range("E" & i).Value - Date <= 60 Then
Range("E" & i).Interior.ColorIndex = 3
Else

End If
End If
End If
Next i
End Sub
 
Upvote 0
^ Even better :P

Another version that works to your constraints
Code: 
Sub findWithin60Days()
    Dim i%
    For i = 7 To 125
        If Range("E" & i).Value <> "" Then
            On Error GoTo oops
            If Range("E" & i).Value - Date <= 60 Then
                Range("E" & i).Interior.ColorIndex = 3
            End If
        End If
oops:
    Next i
End Sub

but yours is better coding practice.
 
Upvote 0
That works too!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! THANK YOU FOR YOU HELP!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! this one has been plaguing me two days now and I was pretty sure the loop was right though for the life of me couldn't figure out where the type mismatch was coming in :):):):)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Conditional Formatting Using VBA
2
Replies
15
Views
278
DanteAmor
DanteAmor
J
  • Question Question
Multiple conditional formatting
Replies
6
Views
278
StephenCrump
S
S
  • Solved
count condition cell color
Replies
5
Views
122
Trebor76
T
J
  • Question Question
Conditional formatting Coloring due dates
Replies
4
Views
194
JAZ91
J
H
  • Solved
VBA Font Conditional Formatting based on cell value change - but it doesn't trigger
Replies
3
Views
76
hcgood
H

Forum statistics

Threads
1,224,521
Messages
6,179,286
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top