Highlight matches between 2 columns of delimited values

[dougebowl]

First off, a huge thank you for any assistance that can be provided.

I searched the forums and found the code below to highlight values which match (seems to be single values in each column). How could this be modified to compare delimited values to highlight the matching values between the 2 columns. Data will always be a single value or multiple values delimited with a semi-colon ";". I am looking to determine if there are 'conflicting' values, so the code only needs to analyze cells in column C which contain a semi-colon (if there is only one value, there cannot be a conflict).

I would like the matching values to be highlighted in Red (instead of Green). I have included a screen shot of my data. Basically, column E contains the conflicting values, and for column C, if there are matching values to 2 or more of the values in column E, color code the values in Red font color.

Data: The number of rows in Column C and Column E can be different every time I need to do the comparison, so need the code to determine the last row for both columns to compare all the rows.

Sub Nanditha()
Dim Cl As Range
Dim Dic As Object

Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = 1
With Sheets("dnd")
For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Dic(Cl.Value) = Empty
Next Cl
End With
With Sheets("cc5")
For Each Cl In .Range("P2", .Range("P" & Rows.Count).End(xlUp))
If Dic.Exists(Cl.Value) Then Cl.Font.Color = rgbLimeGreen
Next Cl
End With
End Sub

 

[Zot]

It is not really clear.

You want to compare if value in column C existed in column E and highlight matching in column E?
For row 3 in column C, the whole phrase need to match or separate into two phrases?

Like this for one example?

 

[dougebowl]

It is not really clear.

You want to compare if value in column C existed in column E and highlight matching in column E?
For row 3 in column C, the whole phrase need to match or separate into two phrases?

Like this for one example?

I need to compare the individual values in column C to those in column E. If there are two values that match any two values in column E. Column E is the list of ‘conflicting’ combinations of values. Therefore, I need those conflicting values highlighted in column C. Please let me know if there are further questions.

 

[Zot]

In sample below A,B as individual value or A and B are separate values?

How would you highlight the sample below?

Sample Data Zaki.xlsm
	A	B	C
1	A		A,D
2	A,B		A,C
3	C		D
4	D		F
5	D,F		C
Sheet2

 

[bebo021999]

Try:

Sub test()
Application.ScreenUpdating = False
Dim s As Variant
LrA = Cells(Rows.Count, "A").End(xlUp).Row
LrE = Cells(Rows.Count, "E").End(xlUp).Row
Range("A2:A" & LrA).Interior.Color = xlNone
    For Each A In Range("A2:A" & LrA)
        s = Split(A, ";")
        If Not IsEmpty(s) Then
            For Each E In Range("E2:E" & LrE)
            k = 0
                For i = 0 To UBound(s)
                    If InStr(1, E, s(i)) > 0 Then k = k + 1
                Next
                    If k > 1 Then A.Interior.Color = 255
            Next
        End If
    Next
Application.ScreenUpdating = True
End Sub

 

[dougebowl]

In sample below A,B as individual value or A and B are separate values?

How would you highlight the sample below?

Sample Data Zaki.xlsm
	A	B	C
1	A		A,D
2	A,B		A,C
3	C		D
4	D		F
5	D,F		C
Sheet2

Based on your sample, I realize I might not have been 100% clear in what I need highlighted. I need to highlight if two or more values in column A are identified in column C. Column C will always contain less rows than column A as column C identifies all the conflict combinations that column A needs to be compared against.

Value(s)	Explanation of Highlight	Conflicts
A;B;C	A and B match the combination in C4	A;D;K;W;Z
A	Only one value in column A, so there will not be a conflict	B;C
B	Only one value in column A, so there will not be a conflict	A;B
A;D;K;T;S	A, D, and K match the combination in C2	B
D;E;F	Only D appears in the conflicts column, but, no additional values match. No highlight
	Highlight the combination of values in column A, when two or more of the values match the values in column C

 

[Peter_SSs]

In cell A2, why isn't the "C" red as well since B and C in A2 match the combination in C3?

If A7 was "B;C;K;Z" what, if anything, would get coloured red given that B and C are in C3 and also K & Z are in C2?

 

[bebo021999]

try:

Sub Color()
Dim spL1 As Variant
Dim a, e As Range
Dim LrA, LrE, i, k As Long
LrA = Range("A65000").End(xlUp).Row
LrE = Range("E65000").End(xlUp).Row
Range("A:A").Font.Color = 0
For Each a In Range("A2:A" & LrA)
    spL1 = Split(a, ";")
    For Each e In Range("E2:E" & LrE)
        k = 0
        For i = 0 To UBound(spL1) ' loop through each name in column A
        Start = InStr(1, a, spL1(i))
        Lenght = Len(spL1(i))
            If InStr(1, e, spL1(i)) > 0 Then ' check if each name exists
            a.Characters(Start, Lenght).Font.Color = 255
            k = k + 1
            End If
        Next
            If k >= 2 Then GoTo Z
            If k < 2 Then a.Font.Color = 0
    Next
Z:
Next
End Sub

Book2.xlsm
	A	B	C	D	E
1
2	NameA;NameX;NameC				NameA;NameD;NameB;NameW;NameZ;NameC
3	NameA				NameB;NameC
4	NameB				NameA;NameB
5	NameA;NameD;NameK;NameT;NameS				NameB;NameC
6	NameD;NameA;NameZ
Sheet1

 

[dougebowl]

In cell A2, why isn't the "C" red as well since B and C in A2 match the combination in C3?

If A7 was "B;C;K;Z" what, if anything, would get coloured red given that B and C are in C3 and also K & Z are in C2?

You are 100% correct, I obviously was moving too fast. Whenever there are 2 values in column A where those combinations are identified in Column C, they should highlight red. I will give the code you provided a try and let you know if we have a solution. THANK YOU VERY MUCH!!!

 

[Peter_SSs]

I will give the code you provided a try

I didn't provide any code.

.. but I will now.

Sub MarkConflicts()
  Dim RX As Object, M As Object
  Dim ColA As Variant, ColC As Variant
  Dim i As Long, j As Long, k As Long, ubC As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  ColC = Range("C2", Range("C" & Rows.Count).End(xlUp)).Value
  ubC = UBound(ColC)
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    ColA = .Value
    For i = 1 To .Count
      RX.Pattern = ";" & Replace(ColA(i, 1), ";", ";|;") & ";"
      For j = 1 To ubC
        Set M = RX.Execute(";" & Replace(ColC(j, 1), ";", ";;") & ";")
        If M.Count > 1 Then
          For k = 1 To M.Count
            .Cells(i).Characters(InStr(1, ";" & .Cells(i).Text & ";", M(k - 1)), Len(M(k - 1)) - 2).Font.Color = vbRed
          Next k
        End If
      Next j
    Next i
  End With
End Sub

Here are my sample data and results. I have manually entered what I think are the reasons for highlighting.