On Sat, 20 Oct 2007 20:47:03 -0700, "Doug Glancy"
wrote:

Kirk,

Find takes as it's first argument a range. In the case of your code that
range is the Selection. Selection only applies to the active sheet. What
your other two answers are saying is that in order to to work on a sheet
other than the activesheet, you'll have to specify the sheet and the range.
So instead of

Set foundCell = Selection.Find(What:=Format(ChosenDate, "dd mmm yyyy")

you'd have something like:

Set foundCell = Worksheets("Sheet
2").Range("A1:D200").Find(What:=Format(ChosenDate , "dd mmm yyyy")

If you want to search the same cells as the current Selection but on a sheet
other than the Activesheet, I think you could do this:

Dim strSelectionAddress As String
strSelectionAddress = Selection.Address
Set foundCell = Worksheets("Sheet
2").Range(strSelectionAddress).Find(What:=Format( ChosenDate, "dd mmm yyyy")


Thanks Doug, I'll have a play with that. I was initially trying to
make code that worked on the active sheet, also work on two other
sheets, depending on the date entered. Each sheet covers a decade.

I'm using Isdate and checking it's within my range. (LOL that's not
Excels range which I don't really understand) . If the date
doesn't find an absolute match, I calculate the nearest absolute Date
which might be another decade, meaning another sheet.

When you say "Selection only applies to the active sheet." possibly I
could use the code I have after figuring out how to make another sheet
the 'active sheet'.

Thanks -Kirk