Comparing two columns

[termeric]

I'm trying to compare two columns, and highlight any cells which are not present in either column.


I'm using this code, but i can't seem to get it to work if when the ranges are on two different sheets, only if they are on the same sheet. any idea what i need to tweak to get this to work, or thoughts on how to make this run faster?

Sub compare_cols1()

    Dim NameList As Worksheet
    Dim i As Long, j As Long

    Set NameList = Excel.Worksheets("Names")

    Dim rngNames As Range
    Set rngNames = Sheets("MMT").Range("A1", Sheets("MMT").Range("A1").Offset(Rows.count - 1).End(xlUp))
    Dim varNames As Variant              
    varNames = rngNames.Value2

    Dim rngData As Range
    Set rngData = Sheets("QB").Range("A1", Sheets("QB").Range("A1").Offset(Rows.count - 1).End(xlUp))
    Dim varData As Variant
    varData = rngData.Value2

    Application.ScreenUpdating = False

    For i = LBound(varNames) + 1 To UBound(varNames)
        For j = LBound(varData) + 1 To UBound(varData)
            If varNames(i, 1) <> "" Then
                If InStr(1, varData(j, 1), varNames(i, 1), vbTextCompare) > 0 Then
                    NameList.Cells(j, 3).Interior.ColorIndex = 6
                    NameList.Cells(i, 1).Interior.ColorIndex = 6
                    Exit For
                Else
                End If
            End If
        Next j
    Next i

    Application.ScreenUpdating = True

End Sub

 

[JLGWhiz]

If InStr(1, varData(j, 1), varNames(i, 1), vbTextCompare) > 0 Then
                    NameList.Cells(j, 3).Interior.ColorIndex = 6
                    NameList.Cells(i, 1).Interior.ColorIndex = 6
                    Exit For
                Else
                End If

This is highlighting cells on a third sheet, not on either of the two being compared. Where do you want the highlight to appear? Also, what is Sheets("Names") used for? It appears to be extraneous to the purpose of this macro.

 

[termeric]

Thanks. Originally, i had this working on Sheets("names") comparing 2 columns to test it. i needed to change that section to point to the sheets i am using now.

                If InStr(1, varData(j, 1), varNames(i, 1), vbTextCompare) > 0 Then
                    Sheets("QB").Cells(j, 1).Interior.ColorIndex = 6
                    Sheets("MMACT").Cells(i, 1).Interior.ColorIndex = 6

The details are so much harder to focus on with daycare closed and kids at home. i appreciate the help.

 

[JLGWhiz]

Thanks. Originally, i had this working on Sheets("names") comparing 2 columns to test it. i needed to change that section to point to the sheets i am using now.

                If InStr(1, varData(j, 1), varNames(i, 1), vbTextCompare) > 0 Then
                    Sheets("QB").Cells(j, 1).Interior.ColorIndex = 6
                    Sheets("MMACT").Cells(i, 1).Interior.ColorIndex = 6

The details are so much harder to focus on with daycare closed and kids at home. i appreciate the help.

We have all been there. Glad you fixed it.
Regards, JLG

 

[HappierThan]

I'm trying to compare two columns, and highlight any cells which are not present in either column. Did you mean BOTH columns?

G'day to you, Have you considered a non-VBA solution?

For argument sake you place your 2 columns in the same worksheet, lets say Columns A and B from row 2 down.

In another column put a formula in row 2:- = IF(MATCH(B2,$A$2:$A$1000,0)+TRUE,0) and fill down to at least the bottom of the shorter (if any) column.

This formula should give you 2 options:- either a 0 or a #N/A. You then apply a filter to this row, filtering on #N/A and return those only present in a single column.

If you add a SUBTOTAL in a spare column in row 1, you could get total and, when filtered, same or different data. I used this once on a Postcode new Vs old to determine which Postcodes were no longer available. Hope this is worth considering, stay safe and healthy H.