asked on

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

tomvergote

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

gulrast

ASKER

tom, this is a start but I would like something that is easily operable by anyone and shades the cells

SoyYop

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,

gulrast

ASKER

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.

ASKER CERTIFIED SOLUTION

SoyYop

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tomvergote

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)

gulrast

ASKER

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.