Comparing two excel sheets, same workbook, highlight differences

[Doozae]

I am doing my first steps in excel 2007 vba. What I am trying to do is to code a macro that compares my last and my second last excel sheet and highlights the differences in the last one. As you can see below I coded the following and Excel does not display any errors when I use the debug mode, yet it does not mark any cells where a change has been made, which it is suppose to do. Would anyone mind having a look and correct my potential mistakes in the code below. Thank you very much.

<tbody>
</tbody>

<code>Sub compare2sheets() 
Dim sh1 As Worksheet, sh2 As Worksheet 
Dim rCount As Long, cCount As Long 
Set sh1 = Worksheets(Sheets.Count - 1) 
Set sh2 = Worksheets(Sheets.Count) 
rCount = sh1.Cells(Rows.Count, 1).End(xlUp).Row 
cCount = sh1.Cells(Rows.Count, 1).End(xlUp).Row 
Dim r As Long, c As Integer For r = 1 To rCount    
 For c = 1 To cCount        
 If sh1.Cells(r, c) <> sh2.Cells(r, c) Then            
sh2.Cells(r, c).Interior.ColorIndex = 3         
End If     
Next c 
Next r</code>
End Sub

 

[Ragnar1211]

Rather than VBA, you use Conditional formatting, much faster.

But, your code works for me in a simple workbook with only 2 sheets.
Perhaps type in your sheet names?

Sub compare2sheets()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim rCount As Long, cCount As Long
[COLOR=#ff0000]Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")[/COLOR]
rCount = sh1.Cells(Rows.Count, 1).End(xlUp).Row
cCount = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Dim r As Long, c As Integer

For r = 1 To rCount
    For c = 1 To cCount
        If sh1.Cells(r, c) <> sh2.Cells(r, c) Then
        sh2.Cells(r, c).Interior.ColorIndex = 3
        End If
    Next c
Next r

End Sub

 

[Momentman]

rCount and cCount have same definition, whats the idea?

 

[NeonRedSharpie]

rCount and cCount have same definition, whats the idea?

Based on the code, I have a feeling cCount is meant to be columns counted. So:

[COLOR=#333333]Cells(1, Columns.Count).End(xlToLeft).column[/COLOR]