Hello Experts,
Need you assistance to use the below code (or similar approach) to perform the following tasks:
1- Ask to open two workbooks.
2- For each identical sheet name in both workbooks compare contents of each cell in identical named ranges (areas will not be equal and may contain merged cells) and highlight missing contents in both sheets.
Also asked here https://www.myonlinetraininghub.com...m-two-named-ranges-in-two-different-workbooks
Need you assistance to use the below code (or similar approach) to perform the following tasks:
1- Ask to open two workbooks.
2- For each identical sheet name in both workbooks compare contents of each cell in identical named ranges (areas will not be equal and may contain merged cells) and highlight missing contents in both sheets.
VBA Code:
Sub HighlightMissingData()
Dim rangeToUse As Range, singleArea As Range, cell1 As Range, cell2 As Range, i As Integer, j As Integer
Dim SelectionOne As Range, SelectionTwo As Range, WS1 As Worksheet, WS2 As Worksheet
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets("Sheet1").Activate
Range("Workbook1.Sheet1!Ongoing_Activities, Workbook2.Sheet1!Ongoing_Activities").Select
Set rangeToUse = Selection
Cells.Interior.ColorIndex = xlNone
If rangeToUse.Areas.Count <= 1 Then
MsgBox "Please select more than one area."
Else
rangeToUse.Interior.Color = vbYellow
For i = 1 To rangeToUse.Areas.Count
For j = i + 1 To rangeToUse.Areas.Count
For Each cell1 In rangeToUse.Areas(i)
For Each cell2 In rangeToUse.Areas(j)
If cell1.Value = cell2.Value Then
cell1.Interior.ColorIndex = xlNone
cell2.Interior.ColorIndex = xlNone
End If
Next cell2
Next cell1
Next j
Next i
End If
End Sub
Also asked here https://www.myonlinetraininghub.com...m-two-named-ranges-in-two-different-workbooks
Last edited by a moderator: