Check this out!

[Ralph M]

I would like to write my code in WorkSheet Change to check for an expiration date. "Z4" is a user entered date, "AA3" and "AB3" are user entered dates. When the user enters data into the cells under "AA" and "AB", Excel checks the two dates above and if one of them are expired, MsgBox. I can't write it to work correctly. Please help?

 

[bensonsearch]

Hi,
Do you have an example of what the test is for the expiration?

otherwise something generic

dim countrow as long
dim count as long
 
count = 1
countrow = sheets("Sheet1").rows.count
 
do until count = countrow
if sheets("Sheet1").range("AA"&count) = 'a check then
msgbox ("hi")
else
'do code
end if
 
count = count + 1
loop

 

[Ralph M]

When the user enters data into the Cell, the code checks the two dates to see if they are > now(). If they have expired, then magbox.

 

[bensonsearch]

ok so something like the below

dim countrow as long
dim count as long
 
count = 1
countrow = sheets("Sheet1").rows.count
 
do until count = countrow
if sheets("Sheet1").range("AA"&count) > now() then
msgbox ("hi")
else
'do code
end if
 
count = count + 1
loop

 

[Ralph M]

OK, how about, when the user enters data, check the date in both cells, if it is < now() then msgbox? My code is :

If Target < -0 And Target.Column = 27 Or Target.Column = 28 Or Target.Column = 29 Or Target.Column = 30 And Not Target.Address = "$Z$4" And Not Target.Address = "$AA$2:$AD$4" Then


MsgBox ActiveSheet.Name & " 's Time has EXPIRED! Please update the FMLA dates."

But everytime I enter data, the msgbox appears, even if the dates in the two cells are in the future.

 

[Ralph M]

If Target < -0 And Target.Column = 27 Or Target.Column = 28 Or Target.Column = 29 Or Target.Column = 30 And Not Target.Address = "$Z$4" And Not Target.Address "= "$AA$2:$AD$4" and activecell.range ("Z4") < -0 or activecell.range ("AA4") < -0 then


MsgBox ActiveSheet.Name & " 's Time has EXPIRED! Please update the FMLA dates."
Else