This should be easy and I HAD it working. I need to compare columns on 2 sheets: E in the first sheet to A in the second sheet. If either sheet has a value that's not found in the other sheet, I need to highlight it bright pink. I'm attaching a very simplified workbook that I've been playing with. Cons Inquiry is the first sheet, with data in E; Roster of trailer is the second sheet, with data in A; Sheets 3 and 4 show what I want to end up with. In real life, the sheets are in 2 different workbooks, though.
I had this macro working on this small sample within 1 workbook (thought I'd start simple). But when I tried to adapt it to work on 2 different books, it failed. Now I've tried so many different things that I can't even remember how I had it when it worked! I either wind up with highlighting on only 1 of the sheets - both A and E, or no highlighting at all.
Can anyone see what (no doubt is some stupid thing) I've overlooked? I'm feeling like a dunce; been working on this for over 4 hrs!
Sub CompareRanges()
'Jenny07232014
Dim WorkRng1 As Range, WorkRng2 As Range, Rng1 As Range, Rng2 As Range
xTitleId = "Select workbooks"
Set WorkSht1 = Application.InputBox("Cons Inq:", xTitleId, "", Type:=8)
Set WorkSht2 = Application.InputBox("Roster:", xTitleId, Type:=8)
LR1 = WorkSht1.Range("E" & Rows.Count).End(xlUp).Row
Set WorkRng1 = WorkSht1.Range("E1:E" & LR1)
LR2 = WorkSht2.Range("A" & Rows.Count).End(xlUp).Row
Set WorkRng2 = WorkSht2.Range("A1:A" & LR2)
On Error Resume Next
WorkSht1.Activate
For i = 2 To WorkSht1.LR1 Step 1
If IsError(WorksheetFunction.Match(WorkSht1.Range("E" & i.Row).Value, WorkSht2.WorkRng2, 0)) Then
Range("E" & i).Interior.Color = VBA.RGB(255, 102, 255)
End If
Next i
WorkSht2.Activate
For y = 11 To WorkSht2.LR2 Step 2
If IsError(WorksheetFunction.Match(WorkSht2.Range("A" & y.Row).Value, WorkSht1.WorkRng1, 0)) Then
Range("A" & y).Interior.Color = VBA.RGB(255, 102, 255)
End If
Next y
End Sub
Thanks,
Jenny
Bookmarks