# Comparing two lists in two columns

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
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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
Commented:
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

Experts Exchange Solution brought to you by