You can make your range dynamic

Dim rng1 as Range, rng2 as range, rng as Range
Dim sAddr as String
With Worksheets("Data")
set rng = .Range(.Cells(2,1),.Cells(2,1).End(xldown))
End With

set rng1 = rng.Find("name")
if not rng1 is nothing then
sAddr = rng1.Address
do
if rng2 is nothing then
set rng2 = rng1
else
set rng2 = union(rng2,rng1)
end if
set rng1 = rng.FindNext(rng1)
Loop until rng1.Address = sAddr
End if

' now rng2 should hold a list of references to the cells in column A that
contain the search string ("name"

You will have to use a trigger event to run the macro. Afraid a Hover isn't
included:

Chip Pearson's page on events
http://www.cpearson.com/Excel/events.htm

--
Regards,
Tom Ogilvy

"Brandon" wrote in message
...
Sheet 1. A - name. B - start of vacation. C - end of vacation.
Sheet 2. A - name. B - start of vacation. C - end of vacation. D -
alternate
contact person.

when i hover over a name in sheet 1, or click on it (or whatever, doesn't
matter), i need it to find the same entry in sheet 2 (name, vac start, vac
end) and then tell me the alternate contact person. BUT, each name may
occur
more than once in column A, so i can't use vlookup to find the first
occurence, since it may not be the correct occurence.

secondly, it must then search sheet 2 column A (or sheet 1 column A) to
find
the name of the alternate contact person, and see if they are on vacation
at
the same time as the initial person selected, and notify if there is a
conflict.

sheet 1 and sheet 2 columns A B and C are identical, BUT the rows are not
the same. each person in sheet 1 only takes up 1 row, but each person in
sheet 2 may take up multiple rows (so i can't just jump over to sheet 2
and
use the same row, i have to search through the list).

any help appreciated! i could probably do it with loops, my own "lookup"
function, but since the total rows that will eventually be used is an
unknown
quantity, i have to give it an insane number like 1000 and that makes it
very
slow. if only vlookup was non-terminating!