VBA Search & Match In Another sheet

[Danny2622]

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

 

[ravishankar]

Hi
paste the following codes in the macro window ( Alt F11)

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]

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)

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]

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