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