The following macro is working as expected, however, for comparing two named ranges on the same sheet. What I need is to compare ALL named ranges on two different workbooks that both have identical named ranges.
Sub CompareRanges()
Dim rangeToUse As Range, singleArea As Range, cell1 As Range, cell2 As Range, i As Integer, j As Integer
Set rangeToUse = Range("sheet1!Ongoing_ACTIVITIES,sheet1!Outstanding_ACTIVITIES")
'Cells.Interior.ColorIndex = 0
rangeToUse.Interior.Color = vbYellow
'rangeToUse.Interior.ColorIndex = 6
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 = 0
cell2.Interior.ColorIndex = 0
End If
Next cell2
Next cell1
Next j
Next i
End Sub