Compare and highlight data between two sheets

[Pitbull_Raven]

Hi all. My first post here. I need help with an issue that I have to deal with daily and that is a huge pain. I work with SAP and I can convert the output to tables that I can use in Excel. Daily, I have to export 2 tables. The first shows me all client orders and respective positions that have to be shipped that day besides other info that doesn't matter. Example as follows:
100001 10 other data other data
100002 10 other data other data
100002 20 other data other data

The second shows me whic orders and positions have already been released for production as follows:
100001 10
100001 20
100002 10
100003 20

What I need is to compare the data in sheet 2 to the data in sheet 1 and highlight the orders that already have a released production order in sheet 2, by putting a 1 in the last column. Example:

100001 10 other data other data 1
100002 10 other data other data 1
100002 20 other data other data

Is this possible?

 

[Pitbull_Raven]

Forgot to mention that I tried some of the suggestions from other threads but didn't get any results. Also tried conditional formating but pretty sure my formula wasn't correct (tried with If and AND). Really stuck. Any ideas?

 

[Damon Ostrander]

Hi Pitbull_Raven,

Here is a macro (CompareSheets) you can run to highlight the differences:

Type CellDiff
   PreviousColorIndex         As Integer
   Address                    As String
End Type
   
Dim SaveDiff()                As CellDiff
Dim ASh                       As Worksheet   'The active worksheet


Sub CompareSheets()
   'This macro compares the contents of all cells on two selected worksheets
   '(ignoring other types of sheets), and highlights the cells that are different
   'on the active worksheet (the worksheet that is on top)
   
   Dim CSh           As Worksheet   'The comparison worksheet
   Dim Cel           As Range
   Dim nDifs         As Long        'The number of differences found
   
   If ActiveWindow.SelectedSheets.Count <> 2 Then
      MsgBox "Two worksheets must be selected", vbExclamation, "Compare Sheets Error"
      Exit Sub
   End If
   
   If ActiveWindow.SelectedSheets(1) Is ActiveSheet Then
      Set ASh = ActiveWindow.SelectedSheets(1)
      Set CSh = ActiveWindow.SelectedSheets(2)
   Else
      Set ASh = ActiveWindow.SelectedSheets(2)
      Set CSh = ActiveWindow.SelectedSheets(1)
   End If
   
   nDifs = 0
   
   For Each Cel In Union(ASh.UsedRange, ASh.Range(CSh.UsedRange.Address))
      If Cel.Value <> CSh.Cells(Cel.Row, Cel.Column) Then
         nDifs = nDifs + 1
         ReDim Preserve SaveDiff(nDifs)
         SaveDiff(nDifs).Address = Cel.Address
         SaveDiff(nDifs).PreviousColorIndex = Cel.Interior.ColorIndex
         Cel.Interior.ColorIndex = 6
      End If
   Next Cel
   
   MsgBox nDifs & " differences found." & vbCrLf & _
                 "Type Ctrl-u to undo highlighting", _
                 vbInformation, "Compare Sheets Results"
                 
   Application.OnKey "^u", "UndoDifHilites"

End Sub

Sub UndoDifHilites()
   
   Dim iCell            As Long
   
   For iCell = 1 To UBound(SaveDiff)
      ASh.Range(SaveDiff(iCell).Address).Interior.ColorIndex = SaveDiff(iCell).PreviousColorIndex
   Next iCell
   
   'release OnKey definition
   Application.OnKey "^u"
   
End Sub

 

[Pitbull_Raven]

Hi Pitbull_Raven,

Here is a macro (CompareSheets) you can run to highlight the differences:

Type CellDiff
   PreviousColorIndex         As Integer
   Address                    As String
End Type
 
Dim SaveDiff()                As CellDiff
Dim ASh                       As Worksheet   'The active worksheet
 
 
Sub CompareSheets()
   'This macro compares the contents of all cells on two selected worksheets
   '(ignoring other types of sheets), and highlights the cells that are different
   'on the active worksheet (the worksheet that is on top)
 
   Dim CSh           As Worksheet   'The comparison worksheet
   Dim Cel           As Range
   Dim nDifs         As Long        'The number of differences found
 
   If ActiveWindow.SelectedSheets.Count <> 2 Then
      MsgBox "Two worksheets must be selected", vbExclamation, "Compare Sheets Error"
      Exit Sub
   End If
 
   If ActiveWindow.SelectedSheets(1) Is ActiveSheet Then
      Set ASh = ActiveWindow.SelectedSheets(1)
      Set CSh = ActiveWindow.SelectedSheets(2)
   Else
      Set ASh = ActiveWindow.SelectedSheets(2)
      Set CSh = ActiveWindow.SelectedSheets(1)
   End If
 
   nDifs = 0
 
   For Each Cel In Union(ASh.UsedRange, ASh.Range(CSh.UsedRange.Address))
      If Cel.Value <> CSh.Cells(Cel.Row, Cel.Column) Then
         nDifs = nDifs + 1
         ReDim Preserve SaveDiff(nDifs)
         SaveDiff(nDifs).Address = Cel.Address
         SaveDiff(nDifs).PreviousColorIndex = Cel.Interior.ColorIndex
         Cel.Interior.ColorIndex = 6
      End If
   Next Cel
 
   MsgBox nDifs & " differences found." & vbCrLf & _
                 "Type Ctrl-u to undo highlighting", _
                 vbInformation, "Compare Sheets Results"
 
   Application.OnKey "^u", "UndoDifHilites"
 
End Sub
 
Sub UndoDifHilites()
 
   Dim iCell            As Long
 
   For iCell = 1 To UBound(SaveDiff)
      ASh.Range(SaveDiff(iCell).Address).Interior.ColorIndex = SaveDiff(iCell).PreviousColorIndex
   Next iCell
 
   'release OnKey definition
   Application.OnKey "^u"
 
End Sub

Thanks. Do I have to chang anything in the script to point to the correct sheets or something?

 

[Damon Ostrander]

Hi again Pit,

Yes, you must select both sheets. The easiest way to do this is to first select whichever of the two sheets you want the differences to be highlighted on (by clicking on its tab). Then select the other sheet without de-selecting the first by using Ctrl-click on its tab.

If you only have the two sheets it is even easier. You can select them both using a right-click on the first, then select "Select All Sheets" in the shortcut menu.

Damon