Your Format Function returns a string version of your date, so what you are
attempting to do is compare a string data type to a date/time data type,
which this doesn't work out. Maybe you could try the following before you
get to the start of your Select Case series.

Dim Date1 as Date
Date1 = Date

Or better yet, sense the Date Function already contains the current date
(Based on your PC date/time), why don't you just skip the variable all
together and just compare the cell to the Date Function.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"Colin" wrote in message
m...
This is going to be so simple compared to some of the posts I've read
trying to track down an answer to my problem but I would be very
grateful for a solution. I've actually achieved a lot with coding for
all sorts of purposes but with no programming background there are
some simple things that just defeat me.

I?m trying to use VBA to delete any rows in a worksheet range which
have a date in the future, while ignoring rows where the target cell
contains other values ? in this case AA, REV, a date in the past, or
is blank.

The problematic section of code is as follows:

Date1 = Format(Now, "dd/mm/yy")
'MsgBox "Date1: " & Date1
Do Until ActiveCell.Value = ""
Select Case ActiveCell.Offset(0, 1).Value
Case "AA", "REV", "", Is < Date1
Case Is Date1
ActiveCell.EntireRow.Delete Shift:=xlUp
End Select
ActiveCell.Offset(1, 0).Select
Loop

It does what I expect for cells with AA, REV, Blank and a date in the
past i.e. nothing, but cells with a date in the future get picked as <
Date1 when they are greater. (I separated out Case Is <Date1 at one
point to try and work out what was going on). In my despair, I checked
the values of Date1 and my target cell using MsgBox and both values
are identical but the Case expression Is Date1 just doesn?t work and
I don?t know why.

I tried having a cell value of 11/09/03 i.e. equal to Date1 and using
the code Case Is = Date1 to see if that would give me any clues but it
just skips over this as well and doesn?t pick it up.

I tried Case Date1 and then Case Else, but AA, REV etc get picked up
as Date1.

Some/most/all of you guys will find this embarrassingly easy to solve
and no sort of challenge to your skills but it?s driving me insane and
I would be really grateful for your help.

Thanks.

Colin