Searchin for dates

This should give you some ideas.

Sub EFG()
Dim rng As Range, rng1 As Range
Dim CritA As Date
Dim sAddr As String
CritA = DateSerial(2006, 1, 17)
With Worksheets("Sheet1")
.Activate
Set rng = .Range(.Cells(1, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
Set rng1 = rng.Find(What:=Format(CritA, _
rng(1).NumberFormat), _
After:=rng(rng.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
sAddr = rng1.Address
Do
Select Case rng1.Offset(0, 1)
Case 1
' add code for a value of 1
rng1.Offset(0, 1).Interior.ColorIndex = 3
Case 2
' add code for a value of 2
rng1.Offset(0, 1).Interior.ColorIndex = 5
Case 7
' add code for a value of 7
Case Else
' add code if doesn't match specified values
End Select

Set rng1 = rng.FindNext(rng1)
Loop While rng1.Address <> sAddr
End If
End Sub


--
Regards,
Tom Ogilvy

"Reggie" <Reggie@discussions.microsoft.com> wrote in message
news:E14D3F1A-2D8B-4113-B118-38A5C3C71431@microsoft.com...
> I have an excel sheet with dates column 1 and a number value in column 2.
For
> example:
>
> 6/5/06 1
> 6/6/06 7
> 6/5/06 1
> 6/6/06 7
> 6/7/06 7
> 6/5/06 1
>
> basically i want excel to look down column 1 find a specific date, look to
> the right for the value and call a different sub according to the
value..the
> problem im having is getting excel to search the everything on the list.
> Right now if it finds one instance of the specified date it stops, i want
it
> to continue down the list, and find all instances of the specified date.
> heres what i have of the code so far.
>
> With Worksheets("Sheet1").Range("A1:A10")
> Set C = .Find(What:=Date + 10)
> Set found = C
>
> If C Is Nothing Then
> x = 1
> End If
>
> If x = 1 Then
> MsgBox ("dont have to send email")
>
> End If
>
> If x = 0 Then
> MsgBox ("have to send email")
>
> If found.Offset(columnoffset:=1).Value = "7" Then
> MsgBox (found)
> A = 1
> Call Group1
>
>
> If found.Offset(columnoffset:=1).Value = "1" Then
> MsgBox ("o")
> B = 1
> Call Group2
> End If
> End If
>
> End If
> End With
>

Thanks Tom, the code worked great!!!!

"Tom Ogilvy" wrote:

> This should give you some ideas.
>
> Sub EFG()
> Dim rng As Range, rng1 As Range
> Dim CritA As Date
> Dim sAddr As String
> CritA = DateSerial(2006, 1, 17)
> With Worksheets("Sheet1")
> .Activate
> Set rng = .Range(.Cells(1, 1), _
> .Cells(Rows.Count, 1).End(xlUp))
> End With
> Set rng1 = rng.Find(What:=Format(CritA, _
> rng(1).NumberFormat), _
> After:=rng(rng.Count), _
> LookIn:=xlValues, _
> LookAt:=xlWhole, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
> If Not rng1 Is Nothing Then
> sAddr = rng1.Address
> Do
> Select Case rng1.Offset(0, 1)
> Case 1
> ' add code for a value of 1
> rng1.Offset(0, 1).Interior.ColorIndex = 3
> Case 2
> ' add code for a value of 2
> rng1.Offset(0, 1).Interior.ColorIndex = 5
> Case 7
> ' add code for a value of 7
> Case Else
> ' add code if doesn't match specified values
> End Select
>
> Set rng1 = rng.FindNext(rng1)
> Loop While rng1.Address <> sAddr
> End If
> End Sub
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Reggie" <Reggie@discussions.microsoft.com> wrote in message
> news:E14D3F1A-2D8B-4113-B118-38A5C3C71431@microsoft.com...
> > I have an excel sheet with dates column 1 and a number value in column 2.
> For
> > example:
> >
> > 6/5/06 1
> > 6/6/06 7
> > 6/5/06 1
> > 6/6/06 7
> > 6/7/06 7
> > 6/5/06 1
> >
> > basically i want excel to look down column 1 find a specific date, look to
> > the right for the value and call a different sub according to the
> value..the
> > problem im having is getting excel to search the everything on the list.
> > Right now if it finds one instance of the specified date it stops, i want
> it
> > to continue down the list, and find all instances of the specified date.
> > heres what i have of the code so far.
> >
> > With Worksheets("Sheet1").Range("A1:A10")
> > Set C = .Find(What:=Date + 10)
> > Set found = C
> >
> > If C Is Nothing Then
> > x = 1
> > End If
> >
> > If x = 1 Then
> > MsgBox ("dont have to send email")
> >
> > End If
> >
> > If x = 0 Then
> > MsgBox ("have to send email")
> >
> > If found.Offset(columnoffset:=1).Value = "7" Then
> > MsgBox (found)
> > A = 1
> > Call Group1
> >
> >
> > If found.Offset(columnoffset:=1).Value = "1" Then
> > MsgBox ("o")
> > B = 1
> > Call Group2
> > End If
> > End If
> >
> > End If
> > End With
> >
>
>
>