Formatting VBA - Why isn't it working on all of them?

[cjlittlet]

I have the following VBA that is supposed to highlight the words in col C that are in col A but it only works on 90% of the cells. The col's match but for some reason, they get skipped. You can see on line 12 it skipped it entirely but on 11 & 13 it worked. Same with lines 17 & 23. I've tried taking out all spaces, clearing all formats first, and copying and pasting into a new workbook and I just can't figure out why it's skipping some of these.

Sub WordCompare()
Dim Cl As Range
Dim x As Long

For Each Cl In Range("C1", Range("C" & Rows.Count).End(xlUp))
x = 0
If Cl.Offset(, -2).value <> "" Then x = InStr(1, Cl.value, Cl.Offset(, -2).value, vbTextCompare)
If x > 0 Then Cl.Characters(x, Len(Cl.Offset(, -2))).Font.Color = vbRed
Next Cl
End Sub

Book1
	A	B	C
5	5280 SOLUTIONS LLC		5280 SOLUTIONS LLC IPACESETTERS LLC TULSA S GREEN COUNTRY STAFFING LLC
6	ABSENTEE SHAWNEE TRIBE SHAWNEE	x	ABSENTEE SHAWNEE TRIBE OF OKFINANCE
7	ACCENT MOVING&STORAGE INC 1992		ACCENT MOVING&STORAGE INC 1992 SCHNEIDER NATIONAL CARRIERS INC% ADP&TALX UCM SERVICES INC
8	ACCOUNTABILITY PARTNERS LLC		FEDERAL EXPRESS CORP ACCOUNTABILITY PARTNERS LLC IPSONG LLC TAKEONE NETWORK CORP PLP ENTERTAINMENT INC FEATURES PROCESSING CA INC BABBIT MITCHELL&CHANCE PLLC
9	ALMS WESTERN AUTO LLC		ALMS WESTERN AUTO LLC LEGEND SENIOR LIVING LLC INC
10	ALORICA AT HOME LLC	x	ALORICA INC WALMART ASSOCIATES INC
11	AMERICAN AIR INC		AMERICAN AIR INC NEXTEP BUSINESS SOLUTIONS IV INC HADLEY HEATING AND AIR LLC
12	AMERICAN AIRLINES INC %DEBTOR IN	x	AMERICAN AIRLINES INC
13	AMERICAN DIABETES ASSOCIATION		TULSA COUNTY PUBLIC FACILITIES AUTHSHANNA DUTTON EXPOSERVE MANAGEMENT CORPORATION AMERICAN DIABETES ASSOCIATION
14	BIG FIVE COMMUNITY SERVICES INC	x	BIG FIVE COMMUNITY SERVICE INC MINDLEAF TECHNOLOGIES INC FEDERAL STAFFING RESOURCES LLC
15	CHEROKEE HILLS VETERINARY CLINIC		CHEROKEE HILLS VETERINARY CLINIC ROSE ROCK VETERINARY HOSPITAL NVA MEMORIAL RD VETERINARY MGMT LLC% COMPUPAY INC VIP PETCARE
16	CHEROKEE NATION		CHEROKEE NATION OF OKLAHOMA LLC
17	CHEROKEE NATION BUSINESS	x	CHEROKEE NATION ENTERTAINMENTS3 MANAGEMENT GROUP LLC
18	CHEROKEE NATION ENTERTAINMENT		CHEROKEE NATION ENTERTAINMENTS3 MANAGEMENT GROUP LLC G4S SECURE SOLUTIONS (USA) INC DATA EXCHANGE INCBESSY KNOX
19	CHEROKEE NATION MISSION SOLUTIONS		CHEROKEE NATION MISSION SOLUTIONSS3 MANAGEMENT GROUP LLC GEOFFREY COLPITTS CONSULTING LLC
20	CHEROKEE NATION OF OKLAHOMA		CHEROKEE NATION OF OKLAHOMA LLC
21	CHEROKEE TEMPS INC		CAPTIVE AIRE SYSTEMS INC%ULTIMATE SOFTWARE GROUP INC CHEROKEE TEMPS INC CARSONS FOOD SERVICE MANAGEMENT LLC
22	CHICKASAW NATION		CHICKASAW NATION TRIBAL GOVERNMENT% KATHY PETTITT
23	CHICKASAW NATION TRIBAL GOVERNMENT	x	CHICKASAW NATIONDIVISION OF COMMERCE ACADEMY LTD %THOMAS&COMPANY SOMRG LLC
Sheet1

 

[rollis13]

Your macro is matching right, in cell A12 you are matching AMERICAN AIRLINES INC %DEBTOR IN which isn't present in C12 since it's 'only' AMERICAN AIRLINES INC so they can't match.
For the same reason neither do rows 6, 10, 14, 17 and 23 match.

 

[cjlittlet]

It's supposed to match whole words so AMERICAN AIRLINES would be in red but the rest wouldn't.

 

[kevin9999]

See if this is closer to what you want. I note that the range you provided starts on row 5 - change that to suit.

Option Explicit
Sub Highlight_Red()
    Dim Rng As Range, c As Range, myString As String, i As Long, j As Long, k As Long, xList, item
    Set Rng = Range("C5", Cells(Rows.Count, "C").End(xlUp))
    
    For Each c In Rng
        myString = c.Offset(, -2).Value
        xList = Split(myString, " ")
        For Each item In xList
            j = InStr(c, item & " ")
            If j Then c.Characters(j, Len(item) + 1).Font.Color = vbRed
        Next item
    Next c
    
End Sub

 

[kevin9999]

Slight improvement on post #4

Option Explicit
Sub Highlight_Red_2()
    Dim Rng As Range, c As Range, i As Long, xList, yList, itemx, itemy
    Set Rng = Range("C5", Cells(Rows.Count, "C").End(xlUp))
    
    For Each c In Rng
        xList = Split(c.Offset(, -2), " ")
        yList = Split(c, " ")
        For Each itemx In xList
            For Each itemy In yList
                If itemy & " " Like itemx & " " Or " " & itemy Like " " & itemx Then
                    For i = 1 To Len(c) - Len(itemx) + 1
                        If Mid(c, i, Len(itemx)) = itemx Then
                            c.Characters(i, Len(itemx)).Font.Color = vbRed
                        End If
                    Next i
                End If
            Next itemy
        Next itemx
    Next c
End Sub

 

[cjlittlet]

Slight improvement on post #4

Option Explicit
Sub Highlight_Red_2()
    Dim Rng As Range, c As Range, i As Long, xList, yList, itemx, itemy
    Set Rng = Range("C5", Cells(Rows.Count, "C").End(xlUp))
   
    For Each c In Rng
        xList = Split(c.Offset(, -2), " ")
        yList = Split(c, " ")
        For Each itemx In xList
            For Each itemy In yList
                If itemy & " " Like itemx & " " Or " " & itemy Like " " & itemx Then
                    For i = 1 To Len(c) - Len(itemx) + 1
                        If Mid(c, i, Len(itemx)) = itemx Then
                            c.Characters(i, Len(itemx)).Font.Color = vbRed
                        End If
                    Next i
                End If
            Next itemy
        Next itemx
    Next c
End Sub

I apologize I didn't get the notice that you had replied! This helpful and I'm using it to run after I do the first one. The problem is that matching things like LLC or INC only causes false positives.