Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello, I would like to know if anyone can suggest the best way to approach this problem.

    I need to be able to highlight a range of data in a workbook and compare it to a range of data in another workbook.

    The workbooks are not identical, but the range in each workbook would be identical in size and format. I just want to be able to identify the differences in cell input.

    I take one workbook and copy a certain range into a list. These workbooks come in from several locations. The list in my master workbook grows as I add to it.
    The list I receive contains deficiencies that the people must correct over time, and resend to me as they make corrections. So I can receive them numerous times before all corrections are completed.

    I want to be able to manually highlight the area in the latest update they send, then some way, compare it to the same range on my master list and show me the differences.

    Hope this makes sense.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Heres some example workbooks

    I would like to be able to highlight C6.E15 in the received workbook and compare to D8.F17 in the Master workbook
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What would be the desired result? Do you want a message box that lists the differences, or would you like the cells within the selected area on the received sheet to be formatted differently if they have no match in the selected area in the master sheet, or vice versa?

  4. #4
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A message Box would be great. Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Keep in mind that the text will be long if there are many differences. Here is a sample macro:

    Code:
    Sub ListDifferences()
      Dim wbk1 As Workbook
      Dim wbk2 As Workbook
      Dim rng1 As Range
      Dim rng2 As Range
      Dim n As Long
      Dim strMsg As String
      Set wbk1 = ActiveWorkbook
      If wbk1.Name = "Master_List.xlsx" Then
    	MsgBox "Please activate the received workbook.", vbInformation
    	Exit Sub
      End If
      Set rng1 = wbk1.Windows(1).Selection
      If rng1.Areas.Count > 1 Then
    	MsgBox "Please select a contiguous range.", vbInformation
    	Exit Sub
      End If
      Set wbk2 = Workbooks("Master_List.xlsx")
      Set rng2 = wbk2.Windows(1).Selection
      If rng2.Areas.Count > 1 Then
    	wbk2.Activate
    	MsgBox "Please select a contiguous range.", vbInformation
    	Exit Sub
      End If
      If Not (rng1.Rows.Count = rng2.Rows.Count And _
    	  rng1.Columns.Count = rng2.Columns.Count) Then
    	MsgBox "Please select ranges of the same size.", vbInformation
    	Exit Sub
      End If
      For n = 1 To rng1.Cells.Count
    	If Not rng1.Cells(n).Value = rng2.Cells(n).Value Then
    	  strMsg = strMsg & vbCrLf & "Received " & rng1.Cells(n).Address & _
    		" <> Master " & rng2.Cells(n).Address
    	End If
      Next n
      If strMsg = "" Then
    	strMsg = "No differences detected."
      Else
    	strMsg = "Differences:" & strMsg
      End If
      MsgBox strMsg, vbInformation
    End Sub

  6. #6
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I may be doing something wrong.
    I opened Master lIst and ran the code and I get a subscript out of range--see attached
    Attached Images Attached Images

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I used the name of the workbook that you attached. Replace both instances of "Master_List.xlsx" with the real name of your "master" workbook.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yep, It was me..Works great thanks!!!!!!!!!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •