[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

Compare 2 excel sheets and mark red where ever changes.

Hi,

I need a macro which can compare 2 sheets and tell me what are the differences between them.

Regards
Sharath
0
bsharath
Asked:
bsharath
  • 4
  • 3
  • 2
1 Solution
 
hiteshgoldeneyeCommented:
Sub sComp()
 Dim s1 As Worksheet, s2 As Worksheet
 Dim i As Integer, j As Integer, k As Integer
 Dim Id As Integer, lastCol As Integer
 Dim fcell As Range
 'assume compared sheets are first and second
 Set s1 = ThisWorkbook.Worksheets(1)
 Set s2 = ThisWorkbook.Worksheets(2)
 Set s3 = ThisWorkbook.Worksheets(3)
 k = 1
 lastCol = s1.UsedRange.Columns.Count
 For i = 2 To s1.UsedRange.Rows.Count
   Id = s1.Cells(i, 1)
   Set fcell = s2.Columns(1).Find(Id)
   If fcell Is Nothing Then
     s3.Cells(k, 1) = "Item ID #" & Id & " is missing at sheet " & s2.Name
     k = k + 1
   Else
     If fcell.Row <> i Then
       s3.Cells(k, 1) = "Item ID #" & Id & " at sheet " & s2.Name & " is shifted on " & Abs(fcell.Row - i) & " rows"
       If (fcell.Row > i) Then
         s3.Cells(k, 1) = s3.Cells(k, 1) & " upwards"
       Else
         s3.Cells(k, 1) = s3.Cells(k, 1) & " downwards"
       End If
       k = k + 1
     End If
     For j = 2 To lastCol
       If s1.Cells(i, j) <> s2.Cells(fcell.Row, j) Then
         s3.Cells(k, 1) = "Item ID #" & Id & " at sheet " & s2.Name & " has difference in field #" & j
         k = k + 1
       End If
     Next j
   End If
  Next i
End Sub
0
 
bsharathAuthor Commented:
Will this copare 2 Sheets in the same file or 2 work sheets.Where will the output come.
0
 
hiteshgoldeneyeCommented:
it will compare sheet1 and sheet2 in the same file and output will come in sheet3 of same file.
Regards
Hitesh
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
bsharathAuthor Commented:
I get this.

Run-time error '13'.
Type mismatch
0
 
Jeroen RosinkCommented:
here another macro,

perhaps this works for you:
Sub WorksheetCompare()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim var1 As Variant, var2 As Variant
Dim cel As Range
Dim i As Long, j As Long, nrows As Long, nCols As Long
Set ws1 = ActiveSheet
Set ws2 = Worksheets(InputBox("Enter name of worksheet to compare to"))
nrows = Application.Max(ws1.UsedRange.Rows.Count, ws2.UsedRange.Rows.Count)
nCols = Application.Max(ws1.UsedRange.Columns.Count, ws2.UsedRange.Columns.Count)
For i = 1 To nrows
For j = 1 To nCols
    var1 = ws1.Cells(i, j)
    var2 = ws2.Cells(i, j)
    If IsError(var1) Or IsError(var2) Then
        ws1.Cells(i, j).Interior.ColorIndex = 4
        ws2.Cells(i, j).Interior.ColorIndex = 4
    Else
        If ws1.Cells(i, j) <> ws2.Cells(i, j) Then
            ws1.Cells(i, j).Interior.ColorIndex = 4
            ws2.Cells(i, j).Interior.ColorIndex = 4
        End If
    End If
Next j
Next i
End Sub

regards,
Jeroen
0
 
bsharathAuthor Commented:
I get this.

---------------------------
Windows Script Host
---------------------------
Today there were 5 ide Files created
---------------------------
OK  
---------------------------


But there are many file.
0
 
bsharathAuthor Commented:
Sorry wrong post ...
0
 
Jeroen RosinkCommented:
Thanks for the grade!
Jeroen
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now