Solved

# Comparing two lists in two columns

Posted on 2013-09-14
Medium Priority
387 Views
I have a list of numbers in column A (A1:A22) that can make up a product.
I have a list of numbers in column E (pasted) that the customer had selected to make up the product, which are the numbers from column A.

Looking for a Macro that looks at E1 then highlights that number in column A, then E2 and highlights that number in column A until it runs into a blank cell in E.

Is it possible when the macro is looking in column E it only looks at the first 11 characters from the left side?

Thanks in advance for any help/suggestions
0
Question by:dgd1212

LVL 3

Assisted Solution

johnnyho_ earned 1000 total points
ID: 39493226
Something like this?

``````Sub CompareColumns()

Dim x As Integer
Dim y As Integer
Dim CutString As String

For x = 1 To Cells(1, 5).End(xlDown).Row

For y = 1 To Cells(1, 1).End(xlDown).Row

CutString = Left(Cells(y, 1), 11)

If Cells(x, 5) = CutString Then

Cells(y, 1).Interior.Color = RGB(153, 255, 102)

End If

Next y

Next x

End Sub
``````

**Code taken from workbook and added to comment by The_Barman (Excel Topic Advisor)
CompareColumns.xlsm
0

LVL 24

Accepted Solution

Steve earned 1000 total points
ID: 39493240
have amended the code provided in jhonnyho_s post to include a dictionary, this will speed up the process time considerably.

``````Sub CompareColumns()

Dim x As Long
Dim d As Object
Set d = CreateObject("scripting.dictionary")

For x = 1 To Cells(1, 5).End(xlDown).Row

If Not d.exists(CStr(Left(Cells(x, 5), 11))) Then

d.Add CStr(Left(Cells(x, 5), 11)), Cells(x, 5).Interior.Color

End If

Next x

For x = 1 To Cells(1, 1).End(xlDown).Row

If d.exists(CStr(Left(Cells(x, 1), 11))) Then

Cells(x, 1).Interior.Color = d.Item(CStr(Left(Cells(x, 1), 11)))

Cells(x, 1).Font.Bold = True

End If

Next x

End Sub
``````

Also I have the colour of the cell in E being stored and transferred to the cell in column A.
This may be helpful, may not.

The attached file should demonstrate the code.
CompareColumnsEdit.xlsm
0

Author Closing Comment

ID: 39498134
Thanks to you both. Works great.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.