Hi all, looking for some help. I'm trying to use a For loop to look through a list of dates in a worksheet and flag cells whose dates are greater than 100 days.


Set Date_Range = Sheets("Sheet1").Range("A1:A500")
YearStartDate = DateSerial(2014, 1, 1)
For Each DateVal In Date_Range
DateAge = DateVal - YearStartDate
If DateAge 100 Then
MsgBox DateVal.Address & "is greater than 100 days
End If
Next DateVal


The code gives an error. Is it because DateVal and YearStartDate are different data types?

Hi,

Am Tue, 30 Dec 2014 13:12:04 -0800 (PST) schrieb :

Hi all, looking for some help. I'm trying to use a For loop to look through a list of dates in a worksheet and flag cells whose dates are greater than 100 days.

try:

Dim Date_Range As Range, DateVal As Range
Dim YearStartDate As Double

Set Date_Range = Sheets("Sheet1").Range("A1:A500")
YearStartDate = DateSerial(2014, 1, 1)
For Each DateVal In Date_Range
DateVal.Interior.Color = _
IIf(DateVal - YearStartDate 100, vbYellow, xlNone)
Next DateVal


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional