highlight unique values between two workbooks

**MarkFltc** · 03-10-2017, 11:35 AM

Hi,

i have code that will highlight rows in workbook 1 if the values in column A of workbook 1 match the values in column A of workbook 2. Now i need to do the opposite and highlight any rows that have a unique value in column A of workbook 1 that is not found in column A of workbook 2.I need to do this using VBA. Below is the code i have which works for highlighting matching values. any help is greatly appreciated.

If Dir("U:\Premier\Macro Logs\Archives\" & facName & " " & Lmonth & ".xlsx") <> "" Then
        Set arcWkbk = Workbooks.Open("U:\Premier\Macro Logs\Archives\" & facName & " " & Lmonth & ".xlsx")
        Application.Visible = False
        
        Set Prevrpt = arcWkbk.ActiveSheet
        Set Currrpt = wkbk.ActiveSheet
    
        For x = 1 To Currrpt.Cells(3, 1).End(xlDown).Row
            For y = 1 To Prevrpt.Cells(3, 1).End(xlDown).Row

            If Currrpt.Cells(x, 1) = Prevrpt.Cells(y, 1) And Currrpt.Cells(x, 11) <> Prevrpt.Cells(y, 11) Then
            Currrpt.Cells(x, 9).Font.Color = -16776961
            Currrpt.Cells(x, 9).Font.Bold = True
            End If
         
        Next y
    Next x
        arcWkbk.Close False
        End If

**Bernie Deitrick** · 03-10-2017, 12:33 PM

You actually have code that will highlight column I if the values in A match but the values in K do not.

Not the best method, but that is what it is doing.

So, perhaps, just change the <> in this line to =

If Currrpt.Cells(x, 1) = Prevrpt.Cells(y, 1) And Currrpt.Cells(x, 11) <> Prevrpt.Cells(y, 11) Then

If that isn't what you want, post back with a better description.

**MarkFltc** · 03-10-2017, 12:44 PM

Sorry i wasn't clear. The code i posted works for what i need it to do. In that code i want to highlight column I if the A = A and I does not = I. What i don't know how to do is to highlight the entire row in workbook 1 if the value in column A in workbook 1 does not match any of the values in column A of workbook 2. so something like

            If Currrpt.Cells(x, 1) <> Prevrpt.Cells(y, 1)  Then
            Currrpt.Cells(x, 1).resize(1,12).Interior.Color = -16776961
             End If

Except that this doesn't work. It highlights every single row since there is obviously going to be a value that doesn't match since it is searching the entire column.

From searching around it seems that i will need to create a custom function but i don't know how to do that properly.

Hope this clarifies.

**MarkFltc** · 03-10-2017, 12:55 PM

Also, you mentioned that i am not using the best method. I am new to VBA so if you don't mind showing me a better method i would really appreciate it.

**Bernie Deitrick** · 03-10-2017, 01:21 PM

Sure - let's look at just your nested loops where you do the check and coloring.

First, to make it so that a value not found results in a highlighted row, using your nested loop for the check:

    For x = 1 To Currrpt.Cells(3, 1).End(xlDown).Row
        For y = 1 To Prevrpt.Cells(3, 1).End(xlDown).Row
            If Currrpt.Cells(x, 1) = Prevrpt.Cells(y, 1) Then GoTo FoundIt
        Next y
        With Currrpt.Cells(x, 1).Resize(1, 12)
            .Font.Color = -16776961
            .Font.Bold = True
        End With
FoundIt:
    Next x

Now, to make it so that we don't use the interior nested loops for the check - this code uses the worksheet function Match, which will return the row number if it finds a value in the column, and an error if it is not found:

    For x = 1 To Currrpt.Cells(3, 1).End(xlDown).Row
        If IsError(Application.Match(Currrpt.Cells(x, 1).Value, Prevrpt.Range("A:A"), False)) Then
            With Currrpt.Cells(x, 1).Resize(1, 12)
                .Font.Color = -16776961
                .Font.Bold = True
            End With
        End If
    Next x

**MarkFltc** · 03-10-2017, 01:49 PM

Awesome! used the Match function and worked like a charm!

Thank you!

highlight unique values between two workbooks

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

highlight unique values between two workbooks

Re: highlight unique values between two workbooks

Re: highlight unique values between two workbooks

Re: highlight unique values between two workbooks

Re: highlight unique values between two workbooks

Re: highlight unique values between two workbooks

Thread Information

Users Browsing this Thread

Similar Threads

Need to compare two columns and highlight unique values.

Compare and identify unique values from different workbooks

Compare Columns & Highlight Unique Values

[SOLVED] Compare table on 2 separate spreadsheets and highlight unique values

[SOLVED] HELP: I just want to highlight the different/unique values in Column B compared against Co

Create Workbooks based on Unique Values from a Column

Macro to find Unique Values and Highlight them?

Bookmarks

Bookmarks

Posting Permissions