higlight new data across sheets with matching in another

Hasson 

Here's a method of doing what you want using a VBA dictionary. That's a bit like an index in a book, arranged alphabettically with two parts- a key and a value. In this case I've combined three cells from sheets RP to form the keys and saved a cell address as the dictionary value (but it could be the count of instances, or location- worksheet name/cell say- like for a word/ page number in a book index).

I've done that since dictionaries have the very useful method .exists(value) to see very quickly if a record like that exists (and where the value can be your "unsplit" values in sheets RP and proc1).

That means there are two main steps to the new code below (in new module 2 and under the code comment lines):

'### 1.  create a dictionary combining cells as a key) and column B address (as dictionary value)

'### 2.  loop through column B of sheets and see if value exists in dictionary

I've added a new button called "Highlight mismatches" to sheet RP and you can click that to run the (commented) code below manually:

Option Base 1
Sub HighByDict()
Dim ws As Worksheet, lRow As Long
Dim i As Long
Dim dict As Object, Cll As Range
Dim s As String, valArray As Variant, n As Long

Set dict = CreateObject("Scripting.Dictionary")
Set ws = Sheets("RP")
ws.Activate
lRow = Range("B" & Rows.Count).End(xlUp).Row

'### 1.  create a dictionary combining cells 9as a key) and column B address (as dictionary value)
For i = 2 To lRow
    If Len(Cells(i, 2)) > 0 Then
        'convert range to an array
        valArray = Application.Transpose(Application.Transpose(Cells(i, 2).Resize(1, 3).Value))
        'create dictionary key by joining array using space as delimiter
        s = Join(valArray, " ")
        'check if dictionary key exists and if not create a new key with value as address in B
        If Not dict.exists(s) Then dict(s) = Cells(i, 2).Address
    End If
Next i

'### 2.  loop through column B of sheets and see if value exists in dictionary
rr = Array("rep1", "proc1")
' loop from first element of rr to last
For i = 1 To UBound(rr)
    ' work with that worksheet
    With Sheets(rr(i))
        'clear any existing colour fills
        .UsedRange.Offset(1, 0).Interior.Color = xlNone
        ' get last row
        lRow = .Range("B" & .Rows.Count).End(xlUp).Row
        'loop down B to last row
        For Each Cll In .Range("B2:B" & lRow)
            ' check if the value is a dictionary key
            If dict.exists(Cll.Value) Then
                ' get cell using address from dictionary value
                With ws.Range(dict(Cll.Value))
                ' ### split value into parts from RP
                    'cell.Resize(1, 3).Value = .Resize(1, 3).Value
                End With
                
                Else
                'if not in dict then make 3 cells red
                Cll.Resize(1, 3).Interior.Color = vbRed
                n = n + 1
            End If
        Next Cll
    End With
Next i

' advise user
MsgBox "Found " & n & " value(s) without an exact match in sheet " & ws.Name

End Sub

Note that you will get 5 (not two) areas in red and below I've given you the reasons as comments:

Sheet RP:

QQW-712  S** CLA7 US ' does not exist in RP



Sheet proc1:

QQW-15 CLA5 EG  ' does not exist in RP (but ...TU does)

QQW-13 CLA11 TR ' says TU not TR in sheet RP

QQW-10 BN CLA10 IT -MM  ' says IT (without -MM) in sheet RP

QQW-15 L/R CLA14 SS230 EG ' says QQW-15 (without L/R) in sheet RP

In your previous files, you have split the combined values into columns. If you want to do that at the same time here, within the new code just uncomment the second line below (as follows):

' ### split value into parts from RP

cell.Resize(1, 3).Value = .Resize(1, 3).Value

However in a Discussion point below (not the original Question) you asked for the sheets to be checked "dynamically" if sheet RP is changed. I've done that by adding extra lines to the macro above (within a With/End With) to clear the colour fills per sheet:

        'clear any existing colour fills

        .UsedRange.Offset(1, 0).Interior.Color = xlNone

and by adding a Worksheet_Change event macro which only checks if there are values in columns B, C and D of a row. That code is behind sheet RP and is shown below, commented so you can see what's happening:

Private Sub Worksheet_Change(ByVal Target As Range)
' do nothing if a cell outside columns B:D was changed
If Intersect(Target, Columns("B:D")) Is Nothing Then Exit Sub

With Cells(Target.Row, 2)
    ' If there are entries in B, C and D...
    If .Value <> "" And .Offset(0, 1).Value <> "" And .Offset(0, 2).Value <> "" Then
        ' ... recheck sheets RP and proc1
        Call HighByDict
    End If
End With
End Sub

(It will give the Message Box from macro HighDict (and you might want to check that it reports 0 mismatches before you do other things with the file- like splitting the cells in RP and proc1)

Finally, I've corrected a typo in your headings (again) to read WAREHOUSE.

Hope this helps.