Comparing two lists in two columns

Posted on 2013-09-14
Medium Priority
Last Modified: 2013-09-16
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
Question by:dgd1212

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

Open in new window

**Code taken from workbook and added to comment by The_Barman (Excel Topic Advisor)
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

Open in new window

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.

Author Closing Comment

ID: 39498134
Thanks to you both. Works great.

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

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.

Join & Write a Comment

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question