# [SOLVED] Excel: Comparing two excel documents and coloring the differences



## jonathynblythe (Apr 14, 2011)

Hello,

My name is Jon and thanks for letting me join here!

What I wanted to know is how I'd do the following:

I have two excel spreadsheets.... they have names, addresses and phone numbers, etc. with their own columns (names are in column A, addresses are in column B, etc.)

This year's data (spreadsheet #2) is different from last year's (spreadsheet #1). I need to know if the phone numbers have different data this year..... and if those phone numbers are even listed in spreadsheet #2. Phone numbers are in column D for both spreadsheets... 

* How do I make spreadsheet #1 highlight the entire row blue in color if the phone number was not included on spreadsheet #2?
* If the phone number is on spreadsheet #2 this year like it should be, how do I make the other cells for that phone number's row turn red if there are any changes this year (i.e. 610-000-0009 is on both spreadsheets, however this year the address changed and I would like to see that cell in that phone number's row turn red....... i.e. 609-000-1111 is on both spreadsheets, but the name and zip code changed and I want both those cells to turn red) 

If there are no changes for any of the phone numbers that are found on both spreadsheets, then that gets left alone. Does this make sense?


Thank you all in advance!
JB


----------



## RSpecianjr (Jan 20, 2010)

*Re: Excel: Comparing two excel documents and coloring the differences*

Hey JB,

Well, I would use a vlookup in conjunction with conditional formatting. It would be easier if all the data was in one workbook. You could have it on two different sheets just to do the analysis.

What column's are you looking at and what information is in those columns?
Also, what version of Excel are you using?

Regards,

Robert D. Specian Jr.


----------



## Glaswegian (Sep 16, 2005)

*Re: Excel: Comparing two excel documents and coloring the differences*

Hi Jon

If you wanted a code alternative to Robert's conditional formatting, then I use this nifty routine for highlighting differences. data needs to be all in the same workbook though, but can be on different sheets. It works for comparing one column against another. You can change the highlight colours to suit.

```
Sub FindDuplicates() 'matches against 2 cols
Dim rng1 As Range
Dim rng2 As Range
Dim bMatch As Boolean
Dim origRng As Range
Dim compRng As Range

On Error Resume Next
Set origRng = Application.InputBox("Choose the first range", "Range 1", Type:=8)
    If origRng Is Nothing Then Exit Sub
Set compRng = Application.InputBox("Choose the second range", "Range 2", Type:=8)
'matches first cell in first range against each cell in second range
'ranges do not need to be equal size
'if there is a match then cell in second range turns green
'if there is not a  match then cell in first range turns red
    For Each rng1 In origRng
        bMatch = False
        For Each rng2 In compRng
            If rng1 = rng2 Then
                bMatch = True
                rng2.Interior.ColorIndex = 4
            End If
        Next rng2
            If bMatch = False Then
                rng1.Interior.ColorIndex = 3
            End If
    Next rng1
End Sub
```


----------



## jonathynblythe (Apr 14, 2011)

*Re: Excel: Comparing two excel documents and coloring the differences*

Wow, thanks for the FAST responses!

Here's a sample of the worksheet:








Column "F" has the phone numbers. As you can see at the bottom, there's another sheet in this workbook where the data is very similar, but not identical. What I want to have happen here is, the phone numbers in sheet 2 have to also be in sheet 3 - if they are not, then I want the cells in sheet 2 (column F) where the phone numbers are colored blue to indicate that they're missing. If the phone numbers are also in sheet 3 like they should be, then I want the other data (name, address, zip code, city, etc) to have the same data that sheets 2's phone numbers have on those same rows. If the data is missing or different, then I want sheet 2's cells to be colored red. How's that?

By the way, Glaswegian, where exactly does one insert a formula like this in an excel document?

Thanks again!
JB


----------



## RSpecianjr (Jan 20, 2010)

*Re: Excel: Comparing two excel documents and coloring the differences*

Hey JB,

Iain's (Glaswegian) code is for a macro not a formula. To insert the code, when you have the workbook open, click ALT+F11 then add a new Module (should be an option for it at the top).

Copy and paste the code above into the blank module that opens. Then hit the play button at the top. 

A requirement of the code is that both the lookup range and the compare range are in the same workbook. It might work if both workbooks are open in the same instance of excel, but no promises on that.

Try it out, if you want a different approach let us know and we can try doing it some other way.

Regards,

Robert D. Specian Jr.


----------



## jonathynblythe (Apr 14, 2011)

*Re: Excel: Comparing two excel documents and coloring the differences*

Ok, the macro asks me for the range. I'm selecting column F (the phone numbers), then it asks me for the second range and then I click column F on the other sheet (the other phone numbers). Then the Excel document freezes up... Am I doing something wrong? Thanks as always!


----------



## Glaswegian (Sep 16, 2005)

*Re: Excel: Comparing two excel documents and coloring the differences*

Hi Jon

It *does* require that both sheets are in the same workbook. The beauty of the code is that the ranges do not have to be equal in size.

You say that Excel freezes - are there any error messages or anything like that? I've used this code on some fairly large ranges and it's always worked fine.

Remember, the code uses green and red as the two main colours...

You could also try Robert's method as an alternative - it would work just as well.


----------



## jonathynblythe (Apr 14, 2011)

*Re: Excel: Comparing two excel documents and coloring the differences*

Yeah there doesn't seem to be any error messages, it just freezes indefinitely. About Robert's method... how exactly does one do that? I'm not familiar with vlookup.

Thanks again!


----------



## Glaswegian (Sep 16, 2005)

*Re: Excel: Comparing two excel documents and coloring the differences*

Just had a thought - don't click to select the complete column. When the box appears asking for Range 1 just click in the first cell and then drag down to the of the range. Repeat for the second range.


----------



## jonathynblythe (Apr 14, 2011)

*Re: Excel: Comparing two excel documents and coloring the differences*

Ah ha! That did the trick... only I'm not able to do 24000 rows otherwise it will crash again. But either way, I can just select what I need and it does just fine!

This code works for when the missing phone is highlighted on that other sheet like I wanted! Only one question left.... When it sees that a phone number IS on the sheet like it should be, how would I have excel check the rest of the row to see if that same data matches what the other sheet's matching phone number has? One example would be, 610-000-9999 was found to be on both sheets, however the name is now different over in that A column for that phone number's row. How would I be able to highlight that cell where the name is indicating that the name has changed from the other sheet?

Thanks again guys for all this help!


----------



## Glaswegian (Sep 16, 2005)

*Re: Excel: Comparing two excel documents and coloring the differences*

Hi Jon

Several ways to do that. We could incorporate some additional code into the existing routine or perhaps look at doing something else if you've already highlighted all the cells of interest.

Would you be able to post a sample workbook (you can remove any sensitive data)?

I'm out tonight so won't be able to have a look until Sunday.


----------



## jonathynblythe (Apr 14, 2011)

*Re: Excel: Comparing two excel documents and coloring the differences*

Sure thing!

If you want to see what the chart looks like, take a look at my post from 04-14-2011 07:29 PM up above. You can click on the graphic and it will show you what the document looks like.

I like the idea of additional code added to the fine example you posted already!

Thanks again as always!


----------



## Glaswegian (Sep 16, 2005)

*Re: Excel: Comparing two excel documents and coloring the differences*

Hi John

See if this works - it is untested though.

```
Sub FindDuplicates2() 'matches against 2 cols
Dim rng1 As Range
Dim rng2 As Range
Dim bMatch As Boolean
Dim origRng As Range
Dim compRng As Range

On Error Resume Next
Set origRng = Application.InputBox("Choose the first range", "Range 1", Type:=8)
    If origRng Is Nothing Then Exit Sub
Set compRng = Application.InputBox("Choose the second range", "Range 2", Type:=8)
'matches first cell in first range against each cell in second range
'ranges do not need to be equal size
'if there is a match then cell in second range turns green
'if there is not a  match then cell in first range turns red
    For Each rng1 In origRng
        bMatch = False
        For Each rng2 In compRng
            If rng1 = rng2 Then
                bMatch = True
                rng2.Interior.ColorIndex = 4
                    If rng1.Offset(0, -4).Value <> rng2.Offset(0, -4).Value Then
                        rng2.Offset(0, -4).Interior.ColorIndex = 3
                    End If
            End If
        Next rng2
            If bMatch = False Then
                rng1.Interior.ColorIndex = 3
            End If
    Next rng1
    
    
End Sub
```
In addition to checking for phone numbers, if it finds a match then it will check column A to see if the names are the same - if not then it will highlight the different name.

Might need some tweaks so please let me know.


----------



## jonathynblythe (Apr 14, 2011)

*Re: Excel: Comparing two excel documents and coloring the differences*

It's telling me, "Compile Error: Expected End Sub". It highlights this line in yellow afterwards: "Sub FindDuplicates2() 'matches against 2 cols"


----------



## Glaswegian (Sep 16, 2005)

*Re: Excel: Comparing two excel documents and coloring the differences*

Hi Jon

Sounds like you may have missed the last line of the code - *End Sub*. Just add that to the end of the code.


----------



## jonathynblythe (Apr 14, 2011)

*Re: Excel: Comparing two excel documents and coloring the differences*

Oops... sorry about that.

Yes, it highlights the changed addresses red. Can I add code so that it does this for the other columns also? (Name, zip code, heading, etc.)

I can't thank you enough! I noticed that the red highlight is on the wrong sheet, I suppose range 1 is how you change it so that it's on the first sheet I select the range for?

Again, thank you SO much!


----------



## Glaswegian (Sep 16, 2005)

*Re: Excel: Comparing two excel documents and coloring the differences*

Hi Jon

Try this - it will highlight the entire row this time. I've also switched the ranges so the highlight should be on the correct sheet - sorry about that.

```
Sub FindDuplicates2() 'matches against 2 cols
Dim rng1 As Range
Dim rng2 As Range
Dim bMatch As Boolean
Dim origRng As Range
Dim compRng As Range

On Error Resume Next
Set origRng = Application.InputBox("Choose the first range", "Range 1", Type:=8)
    If origRng Is Nothing Then Exit Sub
Set compRng = Application.InputBox("Choose the second range", "Range 2", Type:=8)
'matches first cell in first range against each cell in second range
'ranges do not need to be equal size
'if there is a match then cell in second range turns green
'if there is not a  match then cell in first range turns red
    For Each rng1 In origRng
        bMatch = False
        For Each rng2 In compRng
            If rng1 = rng2 Then
                bMatch = True
                rng2.Interior.ColorIndex = 4
                    If rng2.Offset(0, -4).Value <> rng2.Offset(0, -4).Value Then
                        rng1.EntireRow.Interior.ColorIndex = 3
                    End If
            End If
        Next rng2
            If bMatch = False Then
                rng1.Interior.ColorIndex = 3
            End If
    Next rng1
     
End Sub
```


----------



## jonathynblythe (Apr 14, 2011)

*Re: Excel: Comparing two excel documents and coloring the differences*

I sincerely do thank you for all your help! That'll do me well!


----------



## Glaswegian (Sep 16, 2005)

*Re: Excel: Comparing two excel documents and coloring the differences*

You are most welcome - glad it worked for you.


----------

