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