Arithmetic with Cell Dates
 

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?

Arithmetic with Cell Dates
 

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