Comparing to spreadsheets.

Hi Try below:

'This code will compare two excel sheets.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook1 = objExcel.Workbooks.Open("C:\Documents and Settings\mohan.kakarla\Desktop\Docs\1.xls")
Set objWorkbook2 = objExcel.Workbooks.Open("C:\Documents and Settings\mohan.kakarla\Desktop\Docs\2.xls")

Set objWorksheet1 = objWorkbook1.Worksheets(1)

Set objWorksheet2 = objWorkbook2.Worksheets(1)

For Each cell In objWorksheet1.UsedRange
    If cell.Value <> objWorksheet2.Range(cell.Address).Value Then
        cell.Interior.ColorIndex = 3
    Else
        cell.Interior.ColorIndex = 0
    End If
Next

Set objExcel = Nothing

Select allOpen in new window

where &lt stands for Less Than &gt stands for Greater Than.
Hope this helps

Hey it worked... just needed to flip flop the ColorIndex to show the mismatches.  Is it a big edit to only do it for a declared range?  Thinking selecting cell A1  Shift+Ctrl +Down and then Shift Ctrl Right arrow on each sheet and then only compare that?  It could run for a long time if it check every single cell.  Which is fine,  Would like to run some tests on 1000 rows, 5000 rows it see how long it would run.
Please advise and thanks.

If it is just one column A, Then try this:

'This code will compare two excel sheets.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Dim LR As Long
Set objWorkbook1 = objExcel.Workbooks.Open("C:\Documents and Settings\mohan.kakarla\Desktop\Docs\1.xls")
Set objWorkbook2 = objExcel.Workbooks.Open("C:\Documents and Settings\mohan.kakarla\Desktop\Docs\2.xls")

Set objWorksheet1 = objWorkbook1.Worksheets(1)

Set objworksheet2 = objWorkbook2.Worksheets(1)
LR = objworksheet2.Range("A" & Rows.Count).End(xlUp).Row
For Each cell In objWorksheet1.UsedRange
    If cell.Value <> objworksheet2.Range("A1:A" & LR).Value Then
        cell.Interior.ColorIndex = 3
    Else
        cell.Interior.ColorIndex = 0
    End If
Next

Set objExcel = Nothing

Select allOpen in new window

Try below, if you are running on huge data:

Sub CompareTwoSheets()
Dim Wb1 As Workbook, Wb2 As Workbook
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Ws1LR As Long, Ws2LR As Long
Dim c As Range
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With

Set Wb1 = Workbooks.Open("C:\Documents and Settings\mohan.kakarla\Desktop\Docs\1.xls")
Set Wb2 = Workbooks.Open("C:\Documents and Settings\mohan.kakarla\Desktop\Docs\2.xls")
Set Ws1 = Wb1.Worksheets(1)
Set Ws2 = Wb2.Worksheets(1)
Ws1LR = Ws1.Range("A" & Rows.Count).End(xlUp).Row
Ws2LR = Ws2.Range("A" & Rows.Count).End(xlUp).Row

For Each c In Ws1.Range("A1:A" & Ws1LR)
    If c.Value <> Ws2.Range("A1:A" & Ws2LR).Value Then
        c.Interior.ColorIndex = 3
    Else
        c.Interior.ColorIndex = 0
    End If
Next c
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Select allOpen in new window

Too bad it is not just column A.  It needs to stay dynamic.  The one I am testing on, would be, as long as there is a header in row 1, it needs to go off to the right that far and yes if there is still a value in Column A keep going until column A is blank.

I did notice something in testing, if the master has a blank cell and the targets equivalent does not, it does not consider that a mismatch?  So in my scenario, Book3  is the Master and Book1 is the Target.

Sub CompareTwoSsheets()

'This code will compare two excel sheets.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook1 = objExcel.Workbooks.Open("C:\_Excel\CompareSheets\Book1.xls") 'Target file (gets the red)
Set objWorkbook2 = objExcel.Workbooks.Open("C:\_Excel\CompareSheets\Book3.xls") 'Master file

Set objWorksheet1 = objWorkbook1.Worksheets(1)

Set objWorksheet2 = objWorkbook2.Worksheets(1)

For Each cell In objWorksheet1.UsedRange
    If cell.Value >= objWorksheet2.Range(cell.Address).Value Then
        cell.Interior.ColorIndex = 0
    Else
        cell.Interior.ColorIndex = 3
    End If
Next

Set objExcel = Nothing

End Sub

Select allOpen in new window

Ok Try below:

Sub CompareTwoSheets()
Dim Wb1 As Workbook, Wb2 As Workbook
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Ws1LR As Long, Ws2LR As Long
Dim c As Range
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With

Set Wb1 = Workbooks.Open("C:\_Excel\CompareSheets\Book1.xls") 'Target file (gets the red)
Set Wb2 = Workbooks.Open("C:\_Excel\CompareSheets\Book3.xls") 'Master file
Set Ws1 = Wb1.Worksheets(1)
Set Ws2 = Wb2.Worksheets(1)
Ws1LR = Ws1.Range("A" & Rows.Count).End(xlUp).Row
Ws2LR = Ws2.Range("A" & Rows.Count).End(xlUp).Row

For Each c In Ws1.Range("A1:A" & Ws1LR)
    If c.Value <> Ws2.Range("A1:A" & Ws2LR).Value Then
        c.Interior.ColorIndex = 3
    Else
        c.Interior.ColorIndex = 0
    End If
Next c
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Select allOpen in new window

hmm having issues with Ln22  Type mismatch error  Run-time eror '13':  Are you getting the same thing?  Attached are my two sample files that I am testing with.

Sub CompareTwoSheets2()
Dim Wb1 As Workbook, Wb2 As Workbook
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Ws1LR As Long, Ws2LR As Long
Dim c As Range
With Application
'    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With

Set Wb1 = Workbooks.Open("C:\_Excel\CompareSheets\Book1.xls") 'Target file (gets the red)
Set Wb2 = Workbooks.Open("C:\_Excel\CompareSheets\Book3.xls") 'Master file
Set Ws1 = Wb1.Worksheets(1)
Set Ws2 = Wb2.Worksheets(1)
Ws1LR = Ws1.Range("A" & Rows.Count).End(xlUp).Row
Ws2LR = Ws2.Range("A" & Rows.Count).End(xlUp).Row

For Each c In Ws1.Range("A1:A" & Ws1LR)
    If c.Value <> Ws2.Range("A1:A" & Ws2LR).Value Then
        c.Interior.ColorIndex = 3
    Else
        c.Interior.ColorIndex = 0
    End If
Next c
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Select allOpen in new window

Book3.xls
Book1.xls

Add it looks like it is only checking column A?  Ws1LR and Ws2LR.. should that go out to see how many columns it needs to go out?  My sample files really did not have headers in it, but those would match.  This does not to do columns and rows.

YES!!!  EXCEL lent!!!  Works for me too.  Thanks. for the help. -R-

Thanks for the help. -R-

You're Welcome RWayneH! Glad eventually it worked 😊