# 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
Asked:
###### 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:
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
Author Commented:
tom, this is a start but I would like something that is easily operable by anyone and shades the cells
Commented:
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,
Author 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.
Commented:
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.

Commented:
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)
Author 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.