Macro to compare names

[Beep Beep]

I have two columns; A and B with names in them.
I would like to compare the names in column A with column B and
If I find a match, highlight the name in cell B.
And then go down one cell in column A and repeat
Until all names in column A are searched.

 

[Per Jessen]

Hi

Try this

Sub CompareA_B()
Dim TargetRange As Range
Set TargetRange = Range("A1", Range("A1").End(xlDown))
For Each c In TargetRange
If c.Value = c.Offset(0, 1).Value Then
c.Offset(0, 1).Interior.ColorIndex = 6
End If
Next
End Sub

Regards,
Per

 

[Mike H]

Hi,

Right click you sheet tab, view code and paste this in

Sub stantial()
Dim myrange, myrange1 As Range
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Lastrow1 = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set myrange = Range("A1:A" & Lastrow)
Set myrange1 = Range("B1:B" & Lastrow1)
For Each c In myrange
For Each c1 In myrange1
If c.Value <> "" And c.Value = c1.Value Then
c1.Interior.ColorIndex = 3
End If
Next
Next
End Sub

You question wasn't clear about wheter you wanted to simply match adjacent
rows or search all column B for any name in column A so this does the latter.

Mike

 

[Rick Rothstein \(MVP - VB\)]

It sounds like Conditional Formatting, rather than a macro, will do what you
want. Give this a try. On your worksheet, click in B1 and swipe down so as
to select as many rows in that column as you think you will ever need at
maximum. It is important that you start in B1; if you have a header row,
then start in B2 but then change the reference in the formula below from B1
to B2. Now, click Format/Conditional Formatting in Excel's menu bar. On the
dialog box that appears, set the first drop down to "Formula Is" and put
this formula in the second field...

=COUNTIF(A:A,B1)

Next, click the Format button on the dialog, click the Patterns tab and pick
a highlight color. OK your way back to the worksheet. Any duplicates within
the cells that were selected when you clicked on the menu bar will be
highlighted in the color you selected.

Rick

 

[Beep Beep]

Thanks Mike - Perfect:

Now the next step is that in the same workbook I have three columns A; B;
and C and would like to compare (highlight) numbers that are in all three
columns.

 

[Rick Rothstein \(MVP - VB\)]

I still think a Conditional Format will serve you better than a macro. If
you decide to investigate that method, follow the same directions as in my
other post; but, for this condition, start in A1 swipe over to Column C and
downward as many rows as you think you will ever need at maximum in any one
of your columns in order to select a 3-column wide by whatever number of
rows selection. Then use this conditional format formula...

=AND(COUNTIF($A:$A,A1),COUNTIF($B:$B,A1),COUNTIF($C:$C,A1))

Rick

 

[Mike H]

Rick,

I agree 100% that CF is a simpler and more effecient option but as this was
posted in 'Programming' I offer the OP this

Sub sonic()
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Dim MyRange1, MyRange2, MyRange3, cr As Range
Dim fullset As Integer
lastrow1 = Cells(Rows.Count, "A").End(xlUp).Row
lastrow2 = Cells(Rows.Count, "B").End(xlUp).Row
lastrow3 = Cells(Rows.Count, "C").End(xlUp).Row
Set MyRange1 = Range("A1:A" & lastrow1)
Set MyRange2 = Range("B1:B" & lastrow2)
Set MyRange3 = Range("C1:C" & lastrow3)
For Each c1 In MyRange1
If Application.WorksheetFunction.IsNumber(c1) = True Then
For Each c2 In MyRange2
For Each c3 In MyRange3
If c1.Value <> "" And c1.Value = c2.Value And c2.Value =
c3.Value Then
Set cr = Union(c1, c2, c3)
cr.Interior.ColorIndex = 3
Set cr = Nothing
End If
Next
Next
End If
Next
End Sub

Mike

 

[Rick Rothstein \(MVP - VB\)]

I was just trying to nudge the OP to what I thought would be a better
solution for him/her. Just a quick point about one part of your code. This
line

Dim MyRange1, MyRange2, MyRange3, cr As Range

only declared 'cr' as a Range variable... MyRange1, MyRange2 and MyRange3
all will end up being declared as Variants. In VB/VBA, unlike a lot of other
languages, **each** variable must be declared separately as to Type or else
it gets declared as a Variant.

Rick

 

[Mike H]

Rick,

Thanks for the tip on declarations.

Mike

I was just trying to nudge the OP to what I thought would be a better
solution for him/her. Just a quick point about one part of your code. This
line

Dim MyRange1, MyRange2, MyRange3, cr As Range

only declared 'cr' as a Range variable... MyRange1, MyRange2 and MyRange3
all will end up being declared as Variants. In VB/VBA, unlike a lot of other
languages, **each** variable must be declared separately as to Type or else
it gets declared as a Variant.

Rick