Arithmetic with Cell Dates

[mlthornton]

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?

 

[Claus Busch]

Hi,

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

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.