[Zack Barresse]

Btw, the entire code in the module should look like this...

Option Explicit

Sub CheckForReferences(rCell As Range)
    
    Dim ws As Worksheet, i As Long
    Dim rLook As Range, rFind As Range, rRef As Range
    Dim aVals() As String, sType As String
    Dim sTemp As String
    
    Set ws = rCell.Parent
    
    '## Should either be GPB or PQS
    sType = rCell.Offset(0, 1).Value
    
    sTemp = Trim(Trim(rCell.Offset(0, 3).Value) & " " & Trim(rCell.Offset(0, 2).Value))
    aVals = Split(sTemp, " ")
    Set rLook = ws.Columns(1)
    
    If UBound(aVals) < 0 Then Exit Sub
    
    Call TOGGLEEVENTS(False)
    
    For i = LBound(aVals) To UBound(aVals)
        Set rFind = rLook.Find(What:=aVals(i), LookAt:=xlWhole, MatchCase:=True)
        If Not rFind Is Nothing Then
            If rFind.Value = rCell.Value Then GoTo SkipOnce
            Select Case sType
            Case "GPB"
                Set rRef = rFind.Offset(0, 3)
            Case "PQS"
                Set rRef = rFind.Offset(0, 2)
            End Select
            If Len(rRef.Value) <> 0 Then
                If InStr(1, rRef.Value, rCell.Value, vbTextCompare) = 0 Then
                    rRef.Value = rRef.Value & " " & rCell.Value ' aVals(i)
                End If
            Else
                rRef.Value = rCell.Value 'aVals(i)
            End If
SkipOnce:
        End If
    Next i
    
    Call TOGGLEEVENTS(True)
    
End Sub

Public Sub TOGGLEEVENTS(blnState As Boolean)
'Originally written by Zack Barresse
    With Application
        .DisplayAlerts = blnState
        .EnableEvents = blnState
        .ScreenUpdating = blnState
        If blnState Then .CutCopyMode = False
        If blnState Then .StatusBar = False
    End With
End Sub

Sub TestMeNowPlzzz()
    Call CheckForReferences(Cells(ActiveCell.Row, 1))
End Sub

[ab11viva]

Beautiful... I think we've got it.

I'm not even sure if this is desired or not but would it be easy to edit the code to remove references if one was deleted. (i.e. basically the opposite of what you just built) If it's easy, great. If not, you've spent enough time on this and I appreciate your help.

Thanks
Adam

[Zack Barresse]

Well we can certainly add that if you want. It would pretty much require looping through the whole workbook every time though. Are the references going to be deleted very often? Is there a time when they would be? Something we could look for? Would you like a 'clean up tool' so-to-speak, something you could click and it would look up and clean missing references? I'm a little worried about performance if we add that code to what we have, on a change event.

[ab11viva]

Zack,

I think a cleanup tool would be a great idea so that it could be run "on-demand".

You're right that this is not a regular enough occurence for it to be set up to constantly monitor.

Thanks
Adam

[Zack Barresse]

So, I must be confusing myself here. Can you explain to me the logic this should follow, to find these items to clean up?