Highlight Cells on Sheet 1 Where differences are found

[LukeAJN]

I've managed to compare 3 separate ranges on one workbook with 3 single ranges across 3 workbooks. Right now it's written to just pop up with a message box either letting me know the data is the same or the data is different. What I would like to do is for the macro to not only let me know there are differences, but to also highlight where the differences are to me. If also possible I would like to paste the diffenret values from the other 3 sheets say in columns X,,Y,Z.

Sub Macro1()
    
    Dim varDataMatrix() As Variant 'Declares a dynamic array variable
    Dim varDataMatrix2() As Variant 'Declares a dynamic array variable
    Dim varDataMatrix3() As Variant 'Declares a dynamic array variable
    Dim lngArrayCount As Long
    Dim lngArrayCount2 As Long
    Dim lngArrayCount3 As Long
    Dim rngMyCell As Range
    Dim rngMyCell2 As Range
    Dim rngMyCell3 As Range
    Dim wbWorkbookOne As Workbook
    Dim wbWorkbookTwo As Workbook
    Dim wbWorkbookThree As Workbook
    Dim wbWorkbookFour As Workbook
    
    Application.ScreenUpdating = False
    
    Set wbWorkbookOne = Workbooks("PositionTest.xls") 'Assumes the first workbook name is 'PositionTest' and that it's open in the current session.
    Set wbWorkbookTwo = Workbooks("CBNATest.xlsx") 'Assumes the second workbook name is 'CBNATest' and that it's open in the current session.
    Set wbWorkbookThree = Workbooks("CBNDTest.xlsx") 'Assumes the third workbook name is 'CBNDTest' and that it's open in the current session.
    Set wbWorkbookFour = Workbooks("CBNLTest.xlsx") 'Assumes the fourth workbook name is 'CBNLTest' and that it's open in the current session.
    
    'First create an array of the values in the desired range of the first workbook.
    For Each rngMyCell In wbWorkbookOne.Sheets("Positions").Range("H5:H7,H9:H11,H13:H19,H21:H22") 'Workbook one range is B3:B6 on 'Sheet1'.
        lngArrayCount = lngArrayCount + 1
        ReDim Preserve varDataMatrix(1 To lngArrayCount) 'Append the record to the existing array
        varDataMatrix(lngArrayCount) = rngMyCell
    Next rngMyCell
    
    lngArrayCount = 0 'Initialise variable
    
    'Loop through Array elements
    For Each rngMyCell In wbWorkbookTwo.Sheets("CBNA").Range("E3:E17") 'Workbook two range is B2:B5 on 'Sheet1'.
        lngArrayCount = lngArrayCount + 1
        If rngMyCell.Value <> varDataMatrix(lngArrayCount) Then
           GoTo QuitRoutinue
        End If
    Next rngMyCell
    
    
    
    'First create an array of the values in the desired range of the first workbook.
    For Each rngMyCell2 In wbWorkbookOne.Sheets("Positions").Range("H5:H7,H9:H11,H13:H19,H21:H22") 'Workbook one range is F3:F6 on 'Sheet1'.
        lngArrayCount2 = lngArrayCount2 + 1
        ReDim Preserve varDataMatrix2(1 To lngArrayCount2) 'Append the record to the existing array
        varDataMatrix2(lngArrayCount2) = rngMyCell2
    Next rngMyCell2
    
    lngArrayCount2 = 0 'Initialise variable
    
    
    'Loop through Array elements
    For Each rngMyCell2 In wbWorkbookThree.Sheets("CBND").Range("E3:E17") 'Workbook three range is B2:B5 on 'Sheet1'.
        lngArrayCount2 = lngArrayCount2 + 1
        If rngMyCell2.Value <> varDataMatrix2(lngArrayCount2) Then
           GoTo QuitRoutinue
        End If
    Next rngMyCell2
    
    'First create an array of the values in the desired range of the first workbook.
    For Each rngMyCell3 In wbWorkbookOne.Sheets("Positions").Range("H5:H7,H9:H11,H13:H19,H21:H22") 'Workbook one range is J3:J6 on 'Sheet1'.
        lngArrayCount3 = lngArrayCount3 + 1
        ReDim Preserve varDataMatrix3(1 To lngArrayCount3) 'Append the record to the existing array
        varDataMatrix3(lngArrayCount3) = rngMyCell3
    Next rngMyCell3
    
    lngArrayCount3 = 0 'Initialise variable
    
    
    'Loop through Array elements
    For Each rngMyCell3 In wbWorkbookFour.Sheets("CBNL").Range("E3:E17") 'Workbook four range is B2:B5 on 'Sheet1'.
        lngArrayCount3 = lngArrayCount3 + 1
        If rngMyCell3.Value <> varDataMatrix3(lngArrayCount3) Then
           GoTo QuitRoutinue
        End If
    Next rngMyCell3
    
    
    'If we get here both datasets have matched.
    Set wbWorkbookOne = Nothing
    Set wbWorkbookTwo = Nothing
    Application.ScreenUpdating = True
    Erase varDataMatrix() 'Deletes the varible contents, free some memory
    MsgBox "Data is the same.", vbInformation
    Exit Sub
    
QuitRoutinue:

    Set wbWorkbookOne = Nothing
    Set wbWorkbookTwo = Nothing
    Application.ScreenUpdating = True
    Erase varDataMatrix() 'Deletes the varible contents, free some memory
    MsgBox "Data is different.", vbExclamation

 

[offthelip]

You might be interested in this thread which was to solve a very similar problem, ( in this case to find duplicates) . I coded it using variant arrays and the dictionary object which is much faster than the method you are using to loop through each cell. The code can easily changed to detect items that are missing.
Identify the Sheet Name and Row Number of Duplicates