Compare two spreadsheets cell by cell

Hi all. I would like to create a macro that will do the following, the input is any spreadsheets A & B:


- If cell is populated in A and not in B, highlight cell in A with red.
- If cell is populated in B and not in A, highlight cell in B with red.
- If a cell mismatches in A and B, highlight the cell in A & B with Yellow.


I was thinking of searching active rows and comparing them using the InStr() function...but I wonder if there is a quicker way.

Thanks,
Chris
gulrastAsked:
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.

tomvergoteCommented:
have a look here:
http://www.mrexcel.com/archive/VBA/16731.html

It simply loops through cell's and columns and compares them but has some caveats listed which you might run into
gulrastAuthor Commented:
tom, this is a start but I would like something that is easily operable by anyone and shades the cells
SoyYopCommented:
Serve yourselfe:

Option Explicit

Sub ReColor()

  Dim Ws As Worksheet
  Dim Col1%, Col2%
  Dim Color1, Color2, Pattern1, Pattern2
  Dim t%
  Dim c1, c2

  Set Ws = Sheet1

  Col1 = 2
  Col2 = 3

  For t = 1 To 100
   
    c1 = Ws.Cells(t, Col1)
    c2 = Ws.Cells(t, Col2)
    Pattern1 = xlNone
    Pattern2 = xlNone
    Color1 = xlNone
    Color2 = xlNone
   
    If c1 = c2 Then
        If c1 <> "" Then
           Color1 = 3
           Color2 = Color1
        End If
    ElseIf c1 = "" Then
           Color2 = 6
    Else
           Color1 = 6
    End If
   
   
    With Ws.Cells(t, Col1).Interior
         .ColorIndex = Color1
    End With
   
   
    With Ws.Cells(t, Col2).Interior
         .ColorIndex = Color2
    End With
   
  Next

End Sub

Tested and working. Columns are b,c: Also, I clear the formatting on other cells (I think is what you need).

Luck,
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

gulrastAuthor Commented:
SoyYop, we're on the right track. Is there any way to do this for an entire spreadsheet though? That is, compare all of spreadsheet A to spreadsheet B.

Thanks for your help all.
SoyYopCommented:
After a cleanup, I've got that. Just call a function with the required parameters, as shown, the times you need. Very simple and easy to use.

You pass the sheets by parameter (in fact, you can pass the sheet from another workbook), plus the columns on both sheets, and start and lenght of comparision.

I can make it more difficult adding different starts for each sheet, but I'm not sure if you really need it: Now is more simple. Let me know.

Option Explicit


Sub DoCompareSheets()
    Call CompareSheets(Sheet1, Sheet2, 2, 2, 4, 50)
    Call CompareSheets(Sheet1, Sheet2, 3, 3, 4, 50)
    ' Just an idea: To check many columns at a time,
    ' for c=1 to 5: Call CompareSheets(Sheet1, Sheet2, c, c, 4, 50): next t

End Sub

'ws1, ws2:Worksheets
'Col1, Col2: Cols to compare on ws1, ws2 (usually, col1=col2)
'Start: Row to start checking
'Lenght:How many to check (from start to start+lenght)
Sub CompareSheets(ByRef ws1 As Worksheet, ByRef ws2 As Worksheet, Col1%, Col2%, Start%, Length%)
  Dim Color1, Color2
  Dim t%
  Dim c1, c2

  For t = Start To Start + Length
   
    c1 = ws1.Cells(t, Col1)
    c2 = ws2.Cells(t, Col2)
   
    Color1 = xlNone
    Color2 = xlNone
   
    If c1 = c2 Then
        If c1 <> "" Then
           Color1 = 3
           Color2 = Color1
        End If
    ElseIf c1 = "" Then
           Color2 = 6
    Else
           Color1 = 6
    End If
   
   
    With ws1.Cells(t, Col1).Interior
         .ColorIndex = Color1
    End With
   
    'Disable this if you don't want the second spreadsheet to change color too
    'With ws2.Cells(t, Col2).Interior
    '     .ColorIndex = Color2
    'End With
   
  Next

End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tomvergoteCommented:
Now if you combine that solution with some of the checks from my link you're there (if you omit the check's it'll work most of the time but it will result in helpdesk call's from your users)
gulrastAuthor Commented:
this is just a tool i need for myself, so this works fine...i just tweaked it a little to search a whole spreadsheet. the code i settled on is this:

Sub DoCompareSheets()
Dim lastrow As Integer

lastrow = ActiveSheet.UsedRange.Rows.Count
lastcol = ActiveSheet.UsedRange.Columns.Count
Dim colu As Integer


For colu = 1 To lastcol
    Call CompareSheets(Sheet1, Sheet2, colu, colu, 1, lastrow)
Next colu

End Sub

'ws1, ws2:Worksheets
'Col1, Col2: Cols to compare on ws1, ws2 (usually, col1=col2)
'Start: Row to start checking
'Lenght:How many to check (from start to start+lenght)
Sub CompareSheets(ByRef ws1 As Worksheet, ByRef ws2 As Worksheet, Col1%, Col2%, Start%, Length%)
  Dim Color1, Color2
  Dim t%
  Dim c1, c2

  For t = Start To Start + Length
   
    c1 = ws1.Cells(t, Col1)
    c2 = ws2.Cells(t, Col2)
   
    Color1 = xlNone
    Color2 = xlNone
   
    If c1 <> c2 Then
    Color1 = 6
    Color2 = Color1
        If c1 = "" Then
            If c2 <> "" Then
                 Color1 = 3
                 Color2 = Color1
                 End If
        End If
        If c2 = "" Then
            If c1 <> "" Then
                    Color1 = 3
                    Color2 = Color1
                    End If
            End If
    Else
   
    End If
   
   
    With ws1.Cells(t, Col1).Interior
         .ColorIndex = Color1
    End With
   
      With ws2.Cells(t, Col2).Interior
         .ColorIndex = Color2
    End With
   
  Next

End Sub


Thanks for your help everyone.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.