Hi,
Thanks for the reply. I added the code you gave me as a Private subroutine and I get a "Type Mismatch" error. When I do the below steps in your code manually, whiel the rest is taken care of by the macro, I do not get the "Type Mismatch" error.
Should I take out the Errhandle subroutine?
Private Sub test()
On Error Resume Next
With ActiveSheet.UsedRange
.SpecialCells(xlConstants, xlTextValues).ClearContents
.NumberFormat = "0.00"
.SpecialCells(xlCellTypeBlanks).Value = 0
End With
End Sub
' Clear all the indicators that previous comparisons may have set.
Private Sub ClearMarkers()
' Clear Sheet1
Sheet1.Select
Sheet1.Cells.Select
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0
Sheet1.Cells(1, 1).Select ' undo selection of entire sheet
' Clear Sheet2
Sheet2.Select
Sheet2.Cells.Select
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0
Sheet2.Cells(1, 1).Select ' undo selection of entire sheet
End Sub
Private Sub test()
On Error Resume Next
With ActiveSheet.UsedRange
.SpecialCells(xlConstants, xlTextValues).ClearContents
.NumberFormat = "0.00"
.SpecialCells(xlCellTypeBlanks).Value = 0
End With
End Sub
Sub CompareDiff()
On Error GoTo ErrHandle
Call ClearMarkers
' Determine range of used cells, using the highest row and column counts
' found in either of the two sheets.
Dim HighRow As Long
HighRow = Sheet1.UsedRange.Rows.Count
If Sheet2.UsedRange.Rows.Count > HighRow Then
HighRow = Sheet2.UsedRange.Rows.Count
End If
Dim HighCol As Long
HighCol = Sheet1.UsedRange.Columns.Count
If Sheet2.UsedRange.Columns.Count > HighCol Then
HighCol = Sheet2.UsedRange.Columns.Count
End If
' Walk through the cells of both sheets, comparing and changing colors
' if differences are found.
Dim RowIndex As Long
Dim ColIndex As Long
Dim RowFirst As Long
Dim ColFirst As Long
For RowIndex = 1 To HighRow
For ColIndex = 1 To HighCol
' Compare formulas, not "text" or other formatting-affected attributes.
If Sheet1.Cells(RowIndex, ColIndex).Formula <> Sheet2.Cells(RowIndex, ColIndex).Formula Then
If CLng(Sheet1.Cells(RowIndex, ColIndex).Value) <> CLng(Sheet2.Cells(RowIndex, ColIndex).Value) Then
' Determine how to indicate the difference on Sheet1.
If Sheet1.Cells(RowIndex, ColIndex).Text = "" Then
' If the cell contains no text, highlight the empty cell.
Sheet1.Select
Sheet1.Cells(RowIndex, ColIndex).Select
Selection.Interior.ColorIndex = 38
Else
' If the cell contains text, color the text.
Sheet1.Cells(RowIndex, ColIndex).Font.Color = &HFF
End If
' Determine how to indicate the difference on Sheet2.
If Sheet2.Cells(RowIndex, ColIndex).Text = "" Then
' If the cell contains no text, highlight the empty cell.
Sheet2.Select
Sheet2.Cells(RowIndex, ColIndex).Select
Selection.Interior.ColorIndex = 38
Else
' If the cell contains text, color the text.
Sheet2.Cells(RowIndex, ColIndex).Font.Color = &HFF
End If
' If this is the first row containing a difference, remember the cell
' in which the change occurred so we can return to it later.
If RowFirst = 0 Then
RowFirst = RowIndex
ColFirst = ColIndex
End If
End If
End If
Next
Next
' Either report no differences or focus on the first difference found.
If RowFirst = 0 Then
MsgBox "No differences!"
Else
If ThisWorkbook.ActiveSheet.Index = 1 Then
Sheet1.Cells(RowFirst, ColFirst).Activate
End If
If ThisWorkbook.ActiveSheet.Index = 2 Then
Sheet2.Cells(RowFirst, ColFirst).Activate
End If
End If
Exit Sub
ErrHandle:
MsgBox Err.Description
End Sub
