How to check values in each cell from one column to each cell in another column

I got 2 set of values and i need to highl开发者_如何学JAVAight common values (alphanumeric) from 2 columns. Number of rows is in excess of 50,000 rows. Any way to write a code for it? Basicly, i need to check each cell from Col A against each Cell from Col I from A2 to A59000

An idea: use the VBScript Dictionary to avoid Rows * Rows loopings and a module for your experiments:

Attribute VB_Name = "Module1"
' needs Reference to Microsoft Scripting Runtime (for Dictionary)

Option Explicit

Const cnRows = 1000
Const cnLChar = 80
Const cnFNum = 1000
Const cnLNum = 1100

Function IntRange(iFrom, iTo)
    IntRange = iFrom + Fix((iTo - iFrom) * Rnd())
End Function

Sub fill()
    Dim sngStart As Single
    sngStart = Timer
    Dim sheetTest As Worksheet
    Set sheetTest = Sheet2
    Dim nRow, nCol
    For nRow = 1 To cnRows
        For nCol = 1 To 2
            sheetTest.Cells(nRow, nCol) = Chr(IntRange(65, cnLChar)) & IntRange(cnFNum, cnLNum)
        Next
    Next
    With sheetTest.Cells.Interior
       .ColorIndex = 0
       .Pattern = xlSolid
       .PatternColorIndex = xlAutomatic
    End With
    sheetTest.Cells(nRow, 1) = Timer - sngStart
End Sub

Sub bruteForce()
    Dim sngStart As Single
    sngStart = Timer
    Dim sheetTest As Worksheet
    Set sheetTest = Sheet2
    Dim nRow1 As Integer
    Dim nRow2 As Integer
    For nRow1 = 1 To cnRows
        For nRow2 = 1 To cnRows
            If sheetTest.Cells(nRow1, 1) = sheetTest.Cells(nRow2, 2) Then
               With sheetTest.Cells(nRow1, 1).Interior
                    .ColorIndex = 8
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
               End With
            End If
        Next
    Next
    sheetTest.Cells(nRow1, 1) = Timer - sngStart
End Sub

Sub useDict()
    Dim sngStart As Single
    sngStart = Timer
    Dim sheetTest As Worksheet
    Set sheetTest = Sheet2
    Dim dicElms As New Scripting.Dictionary
    Dim nRow As Integer
    For nRow = 1 To cnRows
        dicElms(sheetTest.Cells(nRow, 1).Text) = 0
    Next
    For nRow = 1 To cnRows
        If dicElms.Exists(sheetTest.Cells(nRow, 2).Text) Then
           With sheetTest.Cells(nRow, 2).Interior
                .ColorIndex = 8
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
            End With
        End If
    Next
    sheetTest.Cells(nRow, 2) = Timer - sngStart
End Sub