compare 2 excel sheets to see if they both have the same number

I would like to compare 2 sheets that have identical info from 2 systems to see they match.  How do I do that.  i NEED A FORMULA THAT WILL MATCH THE TWO SHEETS ON THE SAME WORKBOOK.

I am attaching the 2 files for you to see the format.

SHEET 1
COSTCTR      SUBCODE      DEBIT      CREDIT      net
200500        2160      505.00      0.00      505.00
200500        2325      624.08      0.00      624.08
200500        2330      8.00      0.00      8.00
200500        2400      1,550.00      0.00      1,550.00
200500        2400      4,856.05      0.00      4,856.05
200500        2400      3,700.00      0.00      3,700.00
200500        2400      8,483.00      0.00      8,483.00
200500        2400      21,187.50      0.00      21,187.50
200500        2400      2,000.00      0.00      2,000.00
200500        2400      142.51      0.00      142.51
200500        2712      10.84      0.00      10.84

SHEET 2

ACCTNO      SUBCD      debit      credit      net
200500      2160      505.00      0.00      505.00
200500      2325      624.08      0.00      624.08
200500      2330      8.00      0.00      8.00
200500      2400      1,550.00      0.00      1550.00
200500      2400      3,700.00      0.00      3700.00
200500      2400      4,856.05      0.00      4856.05
200500      2400      8,483.00      0.00      8483.00
200500      2400      21,187.50      0.00      21187.50

THANKS
Who is Participating?

Commented:
That's because the color didn't get reset after the first time.

Here is an update that will reset the color

Sub TrackDifferences()
Dim rng As Range, cl As Range, sht2 As Worksheet, sht1 As Worksheet

Set sht2 = Sheets("Sheet2")
Set sht1 = Sheets("Sheet1")

Set rng = Intersect(sht1.UsedRange, sht1.Rows("2:" & Rows.Count)) 'set variable rng to the range selected

Application.ScreenUpdating = False

For Each cl In rng.Cells 'loop through all cells of the selected range
If sht2.Cells(cl.Row, cl.Column) <> cl Then
sht2.Cells(cl.Row, cl.Column).Interior.ColorIndex = 3
sht1.Cells(cl.Row, cl.Column).Interior.ColorIndex = 5
Else
sht2.Cells(cl.Row, cl.Column).Interior.ColorIndex = xlNone
sht1.Cells(cl.Row, cl.Column).Interior.ColorIndex = xlNone
End If
Next

Application.ScreenUpdating = True
End Sub
0

Author Commented:
I am usning MS Excel 2010 where do I put this code.  Help
0

Commented:
Since your title differ, the code should be tweaked a bit (updating the range setting)

Thomas

Sub TrackDifferences()
Dim rng As range, cl As range, sht2 As Worksheet, sht1 As Worksheet

Set sht2 = Sheets("Sheet2")
Set sht1 = Sheets("Sheet1")

Set rng = intersect(sht1.UsedRange,sht1.range("A2:A" & rows.count)  'set variable rng to the range selected

application.ScreenUpdating = False

For Each cl In rng.Cells 'loop through all cells of the selected range
If sht2.Cells(cl.Row, cl.Column) <> cl Then _
sht2.Cells(cl.Row, cl.Column).Interior.ColorIndex = 3
Next

application.ScreenUpdating = True
End Sub
0

Commented:
Go Alt+F11 (Visual Basic Editor)
Insert \ Module
Paste the code
Run the code using Alt+F8 in your workbook, or F5 in the VB Editor

Thomas
0

Author Commented:
I am getting a comile error on this line

Set rng = intersect(sht1.UsedRange,sht1.range("A2:A" & rows.count)  'set variable rng to the range selected
0

Author Commented:
Its working but its highlighting only coiumn red.
0

Author Commented:
only colum A red/
0

Commented:

Update
Sub TrackDifferences()
Dim rng As Range, cl As Range, sht2 As Worksheet, sht1 As Worksheet

Set sht2 = Sheets("Sheet2")
Set sht1 = Sheets("Sheet1")

Set rng = Intersect(sht1.UsedRange, sht1.Rows("2:" & Rows.Count)) 'set variable rng to the range selected

Application.ScreenUpdating = False

For Each cl In rng.Cells 'loop through all cells of the selected range
If sht2.Cells(cl.Row, cl.Column) <> cl Then _
sht2.Cells(cl.Row, cl.Column).Interior.ColorIndex = 3
Next

Application.ScreenUpdating = True
End Sub
0

Author Commented:
What is it suppose to is it going to highlight any differences?

Thanks
0

Commented:
Highlighting the differences on sheet2. Do you want the highlight on sheet1 too?

0

Author Commented:
Yes
0

Author Commented:
2 different colors
0

Commented:
Here it is
Sub TrackDifferences()
Dim rng As Range, cl As Range, sht2 As Worksheet, sht1 As Worksheet

Set sht2 = Sheets("Sheet2")
Set sht1 = Sheets("Sheet1")

Set rng = Intersect(sht1.UsedRange, sht1.Rows("2:" & Rows.Count)) 'set variable rng to the range selected

Application.ScreenUpdating = False

For Each cl In rng.Cells 'loop through all cells of the selected range
If sht2.Cells(cl.Row, cl.Column) <> cl Then _
sht2.Cells(cl.Row, cl.Column).Interior.ColorIndex = 3
sht1.Cells(cl.Row, cl.Column).Interior.ColorIndex = 5
Next

Application.ScreenUpdating = True
End Sub
0

Author Commented:
Sorry to bother you, but at the moment its not highlighting only the bad cells.  Its highlighting everything.  Is there to check the net columns of the two sheets based on the account or costctr and subcode of the two sheets.  Its like a "compare."

Thanks
0

Commented:
This will only highlight the bad cells, what do you mean in your last question?

Sub TrackDifferences()
Dim rng As Range, cl As Range, sht2 As Worksheet, sht1 As Worksheet

Set sht2 = Sheets("Sheet2")
Set sht1 = Sheets("Sheet1")

Set rng = Intersect(sht1.UsedRange, sht1.Rows("2:" & Rows.Count)) 'set variable rng to the range selected

Application.ScreenUpdating = False

For Each cl In rng.Cells 'loop through all cells of the selected range
If sht2.Cells(cl.Row, cl.Column) <> cl Then
sht2.Cells(cl.Row, cl.Column).Interior.ColorIndex = 3
sht1.Cells(cl.Row, cl.Column).Interior.ColorIndex = 5
end if
Next

Application.ScreenUpdating = True
End Sub
0

Author Commented:
What is on sheet 1 should be on sheet 2.  Let me try the new code.

Thanks
0

Author Commented:
see the attached, this is what I am getting now
experts.jpg
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.