Excel - cross referencing 2 work sheets

Hi,

I am getting nowhere trying to figure this one out myself so I'm here looking for your help please.

I have 2 work sheets in excel. One called Production contains data including a column of job ID's. The second sheet called Components just has the job ID's. When I put a job ID into the Components sheet it looks for the matching ID on Production sheet and copies the cell background colour to the ID in Components. This works great with the current code but the problem I'm having is if there are more than one entry with the same ID it only matches the colour of the last entry in Production. I need it to only look at the first entry nearest the top of the sheet as I have the data in Production with the latest entries first (date in descending order). Any ideas?

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsHighlight As Worksheet
    Dim wsData As Worksheet
    Dim rngColor As Range
    Dim rngFound As Range
    Dim KeywordCell As Range
    Dim strFirst As String

    Set wsHighlight = Sheets("Production")
    Set wsData = Sheets("Components")

    With wsData.Columns("A:M")
        For Each KeywordCell In wsHighlight.UsedRange.Columns("B").Cells
            Set rngFound = .Find(what:=KeywordCell.Text, LookAt:=xlWhole, _
                          SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True)
            If Not rngFound Is Nothing Then
                strFirst = rngFound.Address
                Set rngColor = rngFound
                Do
                    Set rngColor = Union(rngColor, rngFound)
                    Set rngFound = .Find(what:=KeywordCell.Text, LookAt:=xlWhole, _
                          SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True)
                Loop While rngFound.Address <> strFirst
                rngColor.Interior.Color = KeywordCell.Interior.Color
            End If
        Next KeywordCell
       
    End With
End Sub

---

if you want the first instance, do not use xlprevious as search direction, change to xlnext and specify after argument as last cell in used range, so it will start at the beginning, or if you are sure the first instance would not be in the first cell, you can use after the first cell

Quote:

---

After Optional Variant. The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface. Note that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn’t searched until the method wraps back around to this cell. If you don’t specify this argument, the search starts after the cell in the upper-left corner of the range.

---

Quote:

---

Originally Posted by westconn1

if you want the first instance, do not use xlprevious as search direction, change to xlnext and specify after argument as last cell in used range, so it will start at the beginning, or if you are sure the first instance would not be in the first cell, you can use after the first cell

---

Thanks for looking at this. I've tried doing this and it see's the first instance but continues until it sees the last, I just can't seem to get it to stop after the first instance is found :confused:

Quote:

---

I need it to only look at the first entry nearest the top of the sheet

---

if you are trying to find all matching ids, which appears to be what the code does, then it should not matter in which order you search, but as you specify you only want the first entry, i do not see why you are using a DO loop that should find and then colour all matching IDs, even if it is not working correctly

Quote:

---

I just can't seem to get it to stop after the first instance is found

---

remove or comment out the DO loop (5 lines of code)