Yes. Match returns an error if it doesn't find the date.

--
Regards,
Tom Ogilvy

"Dan Thompson" wrote in message
...
One question tom why the "If Not IsError(res) Then" line ?
Is it nessecary ?

Dan.

"Tom Ogilvy" wrote:

Find can be problematic with dates. I prefer Match

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long
Dim res as Variant

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
res = Application.Match(clng(bDate), SecondDateRng.Columns(1),0)
if not iserror(res) then
set c = SecondDateRng.Columns(1).Cells(res)
d = c.Row - 1
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub

--
Regards,
Tom Ogilvy

"Dan Thompson" wrote in message
...
I appreciate the Idea of conditional formating guy's, however 2
things..

1) I still don't know why the .Find method does not inclued the 1st
cell
in
the specified search range. (btw only seems to be a problem when
working
with
Date format)

2)I can't conditonaly format the cells do to the fact that my code is
going
to be kind of dynamic in the sense that the ranges will not be fixed
the
will
be gathered from a EditRef control on a form by a user and the ranges
/
worksheets / workbooks could be different every time the program is
run.
Unless there is some way to have vba conditional format the ranges
selected
by the user (real time) however that seems like a bunch of extra
unessicary
steps when my code works fine already except for the one problme of
the
..Find
method not including the first cell in a range as a Match found.

Dan Thompson


"Tom Ogilvy" wrote:

Why not use conditional formatting

Select column C to E and do
Format=Conditional Formatting

C1 should be the activecell in the selection


change cell value is to Formula is

in the textbox put in

=countif(A:A,$C1)0

click the format button and then pattern tab. Select a color

OK out.

--
Regards,
Tom Ogilvy

"Dan Thompson" wrote in
message
...
Ok here is the situation I am having.
I have a worksheet named Sheet1 which column "A" has a bunch of
dates
starting Jan 1, 2000 and going down. Column "C" Also has a bunch
of
dates
starting with Jan 1, 2000 and going down. Column "D" and "E" have
just
regular numerical data. Column "A" is based on 5 day week and
colunm
"C"
is
based on 7 day week. and Row 1 is column hedders. What My codde
should
do
when run is highlight all the dates in column "C" that match the
dates
in
Column "A" and the adjacent columns which contain regular
numerical
data.
My Dates in column "A" run from Jan 1, 2000 to Feb 29, 2000 (5 day
weekday
series)
My Dates in Column "C" run from Jan 1, 2000 to Mar 20, 2002 (7 day
series)

When my code is run it misses highlighting cell "C2" which should
be a
matching date with cell "A2" also in colun "C" Nov 11, 2000 is
highlighted
as
a matching date but doesn't exist in column "A". ..... Anyhow
here
is my
code.

Sub Test()
Dim BaseDateRng As Range
Dim SecondDateRng As Range
Dim cel As Range
Dim c As Range
Dim bDate As Date
Dim d As Long

Set BaseDateRng = Worksheets("Sheet1").Range("a2:a44")
'Range(frm1.RefBaseDate)

Set SecondDateRng = Worksheets("Sheet1").Range("c2:e811")
'Range(frm1.RefSecondDate)

For Each cel In BaseDateRng
bDate = cel.Value
With SecondDateRng.Columns(1)
Set c = .Find(bDate, LookIn:=xlFormulas)
d = c.Row - 1
If Not c Is Nothing Then
With SecondDateRng
.Rows(d).Interior.ColorIndex = 4
End With
End If
End With
Next cel
End Sub