Testing cell values...

[Kevin]

I have a workbook that consists of Sheets with Names and
Dates among other things... What I would like to do is
compare Sheet 1 Name and dates with Sheet 2 Name and
dates, and color the intesecting cell a color...

For example; the [namerange] in Sheet 1 are (A2:A10), the
[Startdate](B2:B10) and the Enddate (C2:C10)

NAME: STARTDATE: ENDDATE:
Smith 06/02/03 06/13/03
Jones 06/02/03 06/06/03
Brown... 06/09/03 06/20/03

Sheet 2 consists of TESTNAME (A2:A10) and DateRange
(B1:H1), DateRange is week ending dates...

TESTNAME: 05/23/03 05/30/03 06/06/03 06/13/03
Smith
Jones
Brown...

What I would like to accomplish is an update button, so
when I update Sheet 1, I can hit the button and it will
search Sheet 2 for the matching name, then test the
Startdate and Enddate in Sheet 1 against the DateRange in
Sheet 2 and color the cells that intersects...

This is what I have so far, but it doesn't work...

Sub Button1_Click()
Set DateRange = Worksheets("Sheet2").Range("B1:H1")
Set Startdate = Worksheets("Sheet1").Range("B2:B10")
Set Enddate = Worksheets("Sheet1").Range("C2:C10")
Set NameRange = Worksheets("Sheet1").Range("A2:A10")
Set TestName = Worksheets("Sheet2").Range("A2:A10")
For Each c In [DateRange]
If c.Value >= Startdate And c.Value <= Enddate Then
If NameRange = TestName Then
Cells
(TestName.Row, .Column).Interior.ColorIndex = 35
End If
End If
Next c

End Sub

Thanks in Advance:
Kevin...