VBA Search & Match In Another sheet

Danny2622

New Member
Joined
Jul 30, 2007
Messages
1
Hey

I need VBA code that searches values entered into column A and B (loads of rows) in Sheet1 and then highlights 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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
paste the following codes in the macro window ( Alt F11)

Code:
Sub sss()
e = 0
x = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
y = Worksheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Row
c = Worksheets("sheet1").Cells(x, 1) & " " & Worksheets("sheet1").Cells(x, 2)
For a = 1 To y
b = Worksheets("sheet2").Cells(a, 1) & " " & Worksheets("sheet2").Cells(a, 2)
If b = c Then
e = e + 1
MsgBox "match exists"
d = "A" & a & ":B" & a
Worksheets("sheet2").Range(d).Interior.ColorIndex = 6
Else
End If
Next a
If e = 0 Then
MsgBox "No match found"
End If
End Sub
enter the animal name and age in col A & b of sheet1 and run the macro. If a match exists, it will highlight it in yellow on sheet 2 else says no match found
Ravi
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
instead of selection_change use [b]change[/b]
if you highlight the cells in sheet2 and if accidentally if you hit some other cell in sheet 2 the highlighting will not be there. so I have colored the relevant cells with red. next time when you change age of some other animal in sheet1 the original coloring in sheet 2 will go away and new coloring will come up.

the macro is (modify to suit you if necessary)

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim animal As String
Dim age As Single
Dim cfind As Range
MsgBox Target.Address
animal = Target.Offset(0, -1)
age = Target
MsgBox animal
MsgBox age
With Worksheets("sheet2").Cells
.Interior.ColorIndex = xlNone
On Error Resume Next
Set cfind = .Find(what:=animal, lookat:=xlWhole)
 MsgBox cfind.Address
 If cfind Is Nothing Then Exit Sub
If cfind.Offset(0, 1) = age Then
.Range(cfind, cfind.Offset(0, 1)).Interior.ColorIndex = 3
End If
End With

End Sub
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Conditional formatting
1) select A1:B5
2) go to [Format] - [Conditional Formatting]
3) enter [Formula Is] box
=And(IsNumber(Match(A1,Sheet2!A:A,0)),B2=Indirect("Sheet2!B"&Match(A1,Sheet2!A:A,0)))
4) select color of the cell
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,102
Messages
5,768,102
Members
425,454
Latest member
khoro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top