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("D465000")
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" & 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("D465000")
> 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