Encuentra duplicates en dos columnas en dos hojas

Necesito comparar dos columnas en dos hojas diferentes y descubrir los duplicates: compare la columna C en 'Hoja2' con la columna E en 'Hoja1'. Como muestra, he usado solo algunos pero:

Column E in Sheet1 has 2,000 rows of data. Column C in Sheet2 has ~ 100 rows of data. 

Capturas de pantalla de hojas: http://postimg.org/image/jtuinkqgz/

Sheet1
# 1 - En realidad a través de Dropbox

Sheeet2
# 2 - En realidad a través de Dropbox

Enlace al file Excel de muestra .

 Option Explicit Sub CompareColumns() '--------------------------------------------------------------------------------------------------- This module loops through two columns in Excel and identifies items without a match. The columns can be on different sheets. ----------- '--------------------------------------------------------------------------------------------------- Dim strCol1 As String 'First Column Location Dim strCol2 As String 'Second Column Location Dim strColResults As String 'Output Column Dim strSheetname1 As String 'First sheet name Dim strSheetname2 As String 'Second sheet name Dim iListStart As Integer 'Row where List Begins Dim strTemp As String Dim i As Integer, j As Integer Dim iLastRow1 As Integer, iLastRow2 As Integer '---Edit these variables---' strSheetname1 = "Sheet1" strSheetname2 = "Sheet2" strCol1 = "A" strCol2 = "C" 'strColResults = "B" iListStart = 1 '--------------------------' iLastRow1 = Sheets(strSheetname1).Range(strCol1 & "50000").End(xlUp).Row iLastRow2 = Sheets(strSheetname2).Range(strCol2 & "50000").End(xlUp).Row 'error check If iListStart > WorksheetFunction.Min(iLastRow1, iLastRow2) Then MsgBox ("List not found. Perform logic check on input variables.") Exit Sub End If Sheets(strSheetname1).Range(strCol1 & iListStart & ":" & strCol1 & iLastRow1).Interior.ColorIndex = 0 Sheets(strSheetname2).Range(strCol2 & iListStart & ":" & strCol2 & iLastRow2).Interior.ColorIndex = 0 strTemp = "<<" If iLastRow2 > iLastRow1 Then 'switch the order strTemp = strCol1 strCol1 = strCol2 strCol2 = strTemp strTemp = strSheetname1 strSheetname1 = strSheetname2 strSheetname2 = strTemp strTemp = ">>" End If 'Identify unmatched items in long column For i = iListStart To WorksheetFunction.Max(iLastRow1, iLastRow2) For j = iListStart To WorksheetFunction.Min(iLastRow1, iLastRow2) If UCase(Sheets(strSheetname2).Range(strCol2 & j)) = UCase(Sheets(strSheetname1).Range(strCol1 & i)) Then 'Range(strColResults & i) = i & " to " & j Exit For ' Stops at first match ElseIf j = WorksheetFunction.Min(iLastRow1, iLastRow2) Then 'Range(strColResults & i) = strTemp Sheets(strSheetname1).Range(strCol1 & i).Interior.Color = 255 End If Next j Next i 'Identify unmatched items in short column If strTemp = "<<" Then strTemp = " >>" Else strTemp = " <<" End If For i = iListStart To WorksheetFunction.Min(iLastRow1, iLastRow2) For j = iListStart To WorksheetFunction.Max(iLastRow1, iLastRow2) If UCase(Sheets(strSheetname1).Range(strCol1 & j)) = UCase(Sheets(strSheetname2).Range(strCol2 & i)) Then Exit For ElseIf j = WorksheetFunction.Max(iLastRow1, iLastRow2) Then 'Range(strColResults & i) = Range(strColResults & i) & strTemp Sheets(strSheetname2).Range(strCol2 & i).Interior.Color = 255 End If Next j Next i End Sub 

¿Cómo puedo encontrar los duplicates en la columna C y tal vez colorear los duplicates de la columna C de Sheet2 con la columna E de Sheet1?

Parece que no es lo que este OP quiere pero parece ser una buena opción para otros con un requisito muy similar: select ColumnC en Sheet2 y HOME> Estilos – Formato condicional, Nueva regla …, Use una fórmula para determinar qué celdas formatear y Formatee los valores donde esta fórmula es verdadera :

 =COUNTIF(Sheet1!E:E,C1) 

Formato ... , select la opción de formatting, OK, OK.

Si quieres una solución VBA, puedes usar esto:

 Sub test() Dim ws1 As Worksheet, ws2 As Worksheet Dim lR1&, lR2 Dim rng As Range, Frng As Range Set ws1 = Worksheets("Sheet1") 'Change this to whatever sheet name Set ws2 = Worksheets("Sheet2") 'Same as above With ws1 lR1 = .Cells(.Rows.Count, 3).End(xlUp).Row End With With ws2 lR2 = .Cells(.Rows.Count, 5).End(xlUp).Row End With For Each rng In ws1.Range("C1", "C" & lR1) 'Edit this to the range where the values are located you want to find the duplicates of Set Frng = ws2.Range("E1", "E" & lR2).Find(what:=rng.Value, LookIn:=xlValues, LookAt:=xlWhole, _ MatchCase:=False, SearchFormat:=False) If Not Frng Is Nothing Then Frng.Interior.Color = 255 End If Next rng End Sub 

Sin embargo, tenga en count que este es un caso sensitivo.