Compare 2 unsorted & unequal columns

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:19 PM GMT

QuestionEdit

- 

I am trying to automate a task in a reconciliation report. the user runs a report that returns 5 columns, the ID number will be in the first column. The user gets a list of ID numbers from another report, which he will insert into the main report as the new first column. (So now there are ID numbers in columns A & B.) These columns are unsorted and likely will not have the same number of ID numbers (rows) in them. For these reasons, conditional formatting would not work. Also, not all users who would have to perform the reconciliation understand how to use that. It really needs to be something that can be accomplished in a few clicks.

What I need is - for each ID number in column B, see if it exists in column A. If it does not, I need to highlight it. I have tried a few times, but I am getting caught in the nested loop... I have only been able to do the first ID number (in B2) correctly, then I blow it.

(Since I can't get that part to work, I haven't even tried to consider also highlighting the items in col A that don't exist in col B.)

Any assistance you can give me will be greatly appreciated.

Crystal


AnswerEdit

Crystal,

this worked for me:

Sub MarkData() Dim sh As Worksheet Dim rA As Range, rB As Range Dim cellA As Range, cellB As Range Set sh = ActiveSheet Set rA = sh.Range("A2", sh.Cells(sh.Rows.Count, "A").End(xlUp)) Set rB = sh.Range("B2", sh.Cells(sh.Rows.Count, "B").End(xlUp)) rA.Interior.ColorIndex = xlNone rB.Interior.ColorIndex = xlNone For Each cellB In rB

 If Application.CountIf(rA, cellB) = 0 Then
   cellB.Interior.ColorIndex = 6
 End If

Next For Each cellA In rA

 If Application.CountIf(rB, cellA) = 0 Then
    cellA.Interior.ColorIndex = 6
 End If

Next End Sub

Advertisement

©2024 eLuminary LLC. All rights reserved.