Highlight differences with multiple values in one cell

[Smithers]

I have two worksheets each with one column I need to compare to the other corresponding worksheet. Each cell has multiple values inside it. I need to highlight the differences, not by highlighting the cell, but highlighting the value itself. I have tried conditional formatting, but can only get it to highlight the entire cell.

Example:
A1: 97,98,99,100,101
B1: 97,99,100,102

End Result:
A1: 97,98,99,100,101
B1: 97,99,100,102

Much thanks in advance.

 

[Weaver]

Differences from and to what?

Your example isn't clear, are you comparing A1 in sheet1 to A1 in sheet2?

Are you comparing A1 with B1 in either sheet?

Are you comparing all numbers in all cells in a column?

Which column, A or B?

or should column B hold the results?

I think a little more explanation may be required.

 

[Smithers]

I'm sorry for any confusion. I tried to keep my question simple initially.

I have been able to reach my ultimate goal using MS Word's document compare after pasting my tables into two different documents. I figured that there had to be some code to write that could complete the same thing in Excel.

Overall, I have two worksheets (TEST and PROD) inside of one workbook. In each, cells C2:C49 contain data with as little as 5 values and as much as a couple hundred values. I need to compare each of those cells to it's respective other cell and vice versa. I need to find which values exist in one and not the other. I then need to highlight the values that do not exist in the cell that I'm comparing it to.

Another example:
TEST!C5 contains: 70,93,94,95,96,97,98,99,104,113,114,
115,118,119,120,121,125,133, 160,163,298,330,331,332,333,334,335,
336,337,352,353,354,393,401,406,419,420,421,427,
PROD!C5 contains: 97,98,99,118,119,120,163,334,335,336,353,354,406,

So from this I need to take what's in TEST, compare it to PROD and determine which values do not exist in PROD.

End result:
TEST!C5: 70,93,94,95,96,97,98,99,104,113,114,
115,118,119,120,121,125,133,160,163,298,330,331,332,333,334,335,
336,337,352,353,354,393,401,406,419,420,421,427

I will need to then compare PROD to TEST as well, but I would just reverse code to complete this. Hopefully this is more clear.

Thank you.

 

[Weaver]

So long as there's a comma at the end (like your sample data), this ought to work

Sub runthis()
    For a = 2 To 49
        compareNumbers Range("test!C" & a), Range("prod!C" & a)
    Next
End Sub
Sub compareNumbers(ByVal testCel As Range, prodCel As Range)
    tc = Split(testCel.Value, ",")
    n = 1
    fc = "," & prodCel.Value
    For Each c In tc
        If c <> "" Then
            i = InStr(fc, "," & c & ",")
            l = Len(c)
            If i = 0 Then
                testCel.Characters(Start:=n, Length:=l).Font.Bold = True
            End If
            n = n + l + 1
        End If
    Next
End Sub

 

[Smithers]

This worked. Thank you for your help.

 

[Weaver]

Thanks for the feedback!