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?
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?
Code:
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