Yooooh aldredd,

You are the great, making thing simple and easier, the code is running fine... Thank You...

BTW, this is just a stating for my workbook, i'm working on the xls and database now...

Good luck...

Quote Originally Posted by aldredd
OK, I decided to have a bit of a play with this - to settle my own curiosity you could say

Anyway, I was right in saying you cant lookup to the left - but offset wasn't the answer - but found a similar solution.

More importantly, I was correct about the frenzied looping thing - the code will go on infinity, re calculating everything over & over again.

Luckily, I tinkered, and found the solution!

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
        Case "$B$4"
            FindPart
        Case "$C$4"
            FindPart2
        Case "$D$4"
            FindPart3
    End Select
End Sub
Ok, this bit is just a tidier way of doing the IF statements.

Sub FindPart()
If Application.ActiveCell.Address = "$B$4" Then
    Range("C4").Value = Application.WorksheetFunction.VLookup(Range("B4"), Sheets("List").Range("C2:D4"), 2, 0)
    Range("D4").Value = Application.WorksheetFunction.VLookup(Range("B4"), Sheets("List").Range("C2:E4"), 3, 0)
End If
End Sub
Sub FindPart2()
Dim r As Long
If Application.ActiveCell.Address = "$C$4" Then
r = Application.WorksheetFunction.Match(Range("C4"), Sheets("List").Range("Company_A_PartNumber"), 0) + 1
    Range("B4").Value = Sheets("List").Cells(r, 3).Value
    Range("D4").Value = Application.WorksheetFunction.VLookup(Range("C4"), Sheets("List").Range("D2:E4"), 2, 0)
End If
End Sub
Sub FindPart3()
Dim i As Long
If Application.ActiveCell.Address = "$D$4" Then
r = Application.WorksheetFunction.Match(Range("D4"), Sheets("List").Range("Company_B_PartNumber"), 0) + 1
    Range("C4").Value = Sheets("List").Cells(r, 4).Value
    Range("B4").Value = Sheets("List").Cells(r, 3).Value
End If
End Sub
Here we have the 2 chucks of code / subs

As you see, the last one uses a formula to find the row you're looking for, and then find the correct match that way.
Most importantly, each sub checks to see if the cell you're working on matches the cell the code relates to, and only continues if this is correct - thus fixing our looping problem!

have fun!