Find instances of value in another sheet, copy row and highlight cells

[CompleteNewb]

I have what I think is a bit of a unique situation here.

I have one sheet with many columns (ShtOrig). I have a second sheet with
just one column, with hundreds of different values. For every value in the
2nd sheet, I need to cycle through all cells in the first sheet, and if that
value is found, copy the row of that value, paste it underneath the value in
the 2nd sheet, and highlight the cell that contains the found value (fill it
with a certain color, let's say Red, whatever the hex value for that is).
All values are text strings

So, if ShtOrig has these rows:

A B C D E
1 Pete Larry October Fire Pete
2 Sunday Tom Salad Dragon Pete
3 Dragon Fire Cindy Game Pick
4 Luck Drink Pete Gone Crazy


And my second sheet has this:

A
1 Pete
2 Fire
3 Game

Then after running the code, in the second sheet I'd have this:

A B C D E
1 Pete
2 Pete Larry October Fire Pete (cells A2
and E2 would be highlighted)
3 Sunday Tom Salad Dragon Pete (cell E 3
would be highlighted)
4 Luck Drink Pete Gone Crazy (cell C4
would be highlighted)
5 Fire
6 Pete Larry October Fire Drink (cell D6
would be highlighted)
7 Dragon Fire Cindy Game Pick (cell B7
would be highlighted)
8 Game
9 Dragon Fire Cindy Game Pick (cell D9
would be highlighted)

Sometimes, I'm sure, a value in the 2nd sheet would not be found in ShtOrig,
so it would just continue to the next value.

Is this possible? Can anyone help with the VBA required? I tried using
several different passes of the Duplicate Master add-in, but it seems no
puzzled-together series of operations will perform what I'm trying to get as
my end result.

Thanks for any help on this, and for reading. Your time and assistance is
greatly appreciated.

 

[gimme_this_gimme_that]

Google: "How do you store data in Excel as a database?" In this
newsgroup.

If you have a recent version of Excel and if you know how to build a
SQL JOIN statement you can do what you want.

The post I referred you to has the syntax you'll need to select from
the two sheets.

You may have to have headers on row 1. Good luck.