Hello,

My worksheet has in column1 numbers (001, 002, 003, etc) and column2
has same type of numbers. But, I need to compare beginning with
column1 cell1 and check each cell in column2 if there is a match. If
column1 cell has no match in column2 then I need to change column1
background color to red.

This is the code I have so far but I'm having trouble figuring out how
to check all of column2 cells if there is a match and then continuing
in column1 cells.

Sub CompareData2()
'Compare Column1 cell to all column2 cells
Dim rngC1 As Range
Dim rngC2 As Range
Dim cf As String
Set rngC1 = Range("C4:C65000")
Set rngC2 = Range("D4:D65000")
With ActiveSheet
For Each c1 In rngC1
c1.Select
For Each c2 In rngC2
c2.Select
If c1.Value = c2.Value Then
MsgBox "MATCH" 'for test purpose
Exit For
Else
c1.Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
Next
Next
End With
End Sub

Thank you for your help,
jfcby

try:
Sub tst()
colC = Cells(65500, 3).End(xlUp).Row
colD = Cells(65500, 4).End(xlUp).Row
On Error GoTo om
For t = 4 To colD
rw = Range("D1:D" & colD).Find(Cells(t, 3), LookIn:=xlValues).Row
Next
End
om:
Cells(t, 3).Interior.ColorIndex = 3
Resume Next
End Sub


"jfcby" skrev:

Hello,

My worksheet has in column1 numbers (001, 002, 003, etc) and column2
has same type of numbers. But, I need to compare beginning with
column1 cell1 and check each cell in column2 if there is a match. If
column1 cell has no match in column2 then I need to change column1
background color to red.

This is the code I have so far but I'm having trouble figuring out how
to check all of column2 cells if there is a match and then continuing
in column1 cells.

Sub CompareData2()
'Compare Column1 cell to all column2 cells
Dim rngC1 As Range
Dim rngC2 As Range
Dim cf As String
Set rngC1 = Range("C4:C65000")
Set rngC2 = Range("D4:D65000")
With ActiveSheet
For Each c1 In rngC1
c1.Select
For Each c2 In rngC2
c2.Select
If c1.Value = c2.Value Then
MsgBox "MATCH" 'for test purpose
Exit For
Else
c1.Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If
Next
Next
End With
End Sub

Thank you for your help,
jfcby

Hello excelent,

Your code give me a starting point to work with. I called your code
from a modeless userform and it closed my form when it finished. If I
deleted End then it continued changing the cell background color to
red past the last cell with data. So to fix this I added a If Then
GoTo statement example below.

[macro]

Sub CompareData3()
With ActiveSheet
ColA = Cells(65500, 1).End(xlUp).Row 'changed
ColB = Cells(65500, 2).End(xlUp).Row 'changed
On Error GoTo om
For t = 4 To ColB 'changed
If t ColA Then GoTo es 'added
rw = Range("B4:B" & ColB).Find(Cells(t, 1),
LookIn:=xlValues).Row 'changed
Next
'deleted End
om:
Cells(t, 1).Interior.ColorIndex = 3
Resume Next
End With
es: 'added
End Sub

[/macro]

Hello excelent,

Thank you for your help and code!

jfcby