Comparing lists

[Sheeny]

Hi,
Any help would be greatly appreciated.

I have two lists, old and new. I used the conditional formatting in
Excel to highlight and bold those in Old that do not appear in New, and
those in New that do not appear in old.

Now, I have two listboxes, one entitled Old and one entitled New. I
want to loop through cells A4:A135 and print those that are bold (
because they dont appear in New) in ListOld and similarly, want to loop
thorugh cells B4:B135 and print those that are bold in ListNew.

After coding this I found that it does not work for fields that are
conditionally bold. So I moved on to compare the lists, and print
those in Old that do not appear in New.

I came up with this:

Private Sub CmdRec_Click()

Dim i As Long

ListOld.Clear

Sheet3.Activate
Sheet3.Range("A4").Select

With Sheet3

For i = 1 To .Range("B" & Rows.Count).End(xlUp).Row
For j = 1 To .Range("A" & Rows.Count).End(xlUp).Row
If Sheet3.Cells(i, "B").Value <> Sheet3.Cells(i,
"A").Value Then
ListOld.AddItem (Sheet3.Cells(i, "A").Value)
End If
Next j
Next i

End With

End Sub

Two problems with this:

1. It starts at A1 but I want it to start at A4
2. It prints the 'old' company NUMEROUS times, I guess everytime it
loops through the loop.

Any ideas on how to get what I want?

Thanks!!!
Sheeny

 

[kkknie]

Here's some code based on your original:

Code
-------------------
Private Sub CmdRec_Click()

Dim i As Long
Dim j As Long
Dim bFound As Boolean

'Finds everything in column A that is not in column B
ListOld.Clear
Sheets("Sheet3").Select
Range("A4").Select

For i = 4 To Range("B65536").End(xlUp).Row
bFound = False
For j = 4 To Range("A65536").End(xlUp).Row
If Range("A" & i).Value = Range("B" & j).Value Then
bFound = True
Exit For
End If
Next
If bFound = False Then ListOld.AddItem Range("A" & i).Value
Next

'Finds everything in column B that is not in column A
ListNew.Clear
Sheets("Sheet3").Select
Range("A4").Select

For i = 4 To Range("B65536").End(xlUp).Row
bFound = False
For j = 4 To Range("A65536").End(xlUp).Row
If Range("A" & j).Value = Range("B" & i).Value Then
bFound = True
Exit For
End If
Next
If bFound = False Then ListNew.AddItem Range("B" & i).Value
Next


End Su

 

[Sheeny]

Thank you so much! That works wonderfully