Ok, I'm not sure on exactaly what you are tying to do but lets assume that you want to compare all of dates from column C to todays date and have cell Q changed to the red if more than 7 days has passed and green if it has been 7 days or less. Then you would use something like this. This should give you an idea of where to start.

Sub Change_Colors()
Dim First_Date As Date
Dim Second_Date As Date
Dim Difference As Long
Dim FinalRow As Long
FinalRow = Range("C65535").End(xlUp).Row

Second_Date = Date

For i = 1 To FinalRow
Range("C" & i).Select
First_Date = Selection.Formula
Difference = DateDiff("d", First_Date, Second_Date)
If Difference > 7 Then
Range("Q" & i).Interior.ColorIndex = 3
Else
Range("Q" & i).Interior.ColorIndex = 10
End If
Next i
End Sub