VBA Search & Match Values In Different Worksheets

[SuperSub123]

Afternoon All,

Im stumped in VBA - I need code that searches values entered into column A and B (loads of rows) in Sheet1 and then highlights the row (in both sheets) if that row of data is present in the same columns but in a different sheet (Sheet2).

I'm working in Workbook_SheetSelectionChange - it has to check if the values exist as soon as the user goes onto the next row.

I've attached pictures as a demo:

Sheet1:

http://img508.imageshack.us/img508/107/ex1qc5.jpg

Sheet2:

http://img76.imageshack.us/img76/4944/ex2as3.jpg

In the example above (Sheet1), as soon as the user finishes typing the age for Cat and hits return , the row Cat is in would be highlighted in both Sheets as the name and age match. The same goes for when the user hits return after typing the dogs age as they both match.

The number of rows is going to be unknown.

Thanks

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WalkerEvans]

Use the macro recorder to record the steps necessary to do this manually, and then check out the resulting code. If you're still stumped after that, post your new code back here and we'll see what we can do.

Hint: It often makes finding things a lot easier if you concatenate the cells you'll be routinely searching (on Sheet 2 in your example) and place this data into a new row outside your normal data area. When your user hits that "Enter" key your macro concatenates that data and then does the lookup.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[SuperSub123]

I've tried alot of things, i haven't got the code as im not allowed to take it out of work (strict policy), but where i work they dont like using alot of memory, so using loops everytime you change cell is not allowed. It has to be implemented using the .find property of a range.

I was just looking for code from scratch, does anybody not have any?

 

[PHV]

i haven't got the code as im not allowed to take it out of work
But you're allowed to be a leech ?

 

[xlbo]

It has to be implemented using the .find property of a range.

So press F1 and enter "FIND". View example, try and write code, post back when you have an actual problem you are stuck on

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question