Avatar of Escanaba
Flag for United States of America asked on

Excel 2007 Comparison VBA


Can someone assist in providing a macro that does the following:
Compares data in sheet 1, starting at column B3, against column B in sheet 2.  If there is no match, the row is highlighted red.  For example, column B contains last names.  When the macro is ran and the name Smith is not found in sheet 2, column B, that row is highlighted.

Microsoft OfficeMicrosoft ExcelVisual Basic Classic

Avatar of undefined
Last Comment

8/22/2022 - Mon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

See attached file for a solution using conditional formatting.

I did not write a macro because it really isn't necessary for this...

Matt Coughlin

This is the macro version

Sub findnames()
' Macro compares names from sheet 1 and sheet 2 then highlights red if not found

n = Range("'Sheet1'!b65536").End(xlUp).Row
m = Range("'Sheet2'!b65536").End(xlUp).Row

For i = 3 To n
    o = Range("'Sheet1'!B" & i).Value
For j = 1 To m
    r = Range("'Sheet2'!B" & j).Value
    If o = r Then
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    GoTo 10
    End If
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
End Sub

Open in new window


Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy


make sure you have a look at my solution, if you haven't already - I guess having immediate results is better (for most situations) than having to press a button.

The fact that I'm saying this doesn't mean I want the points - I think the points went to right person, he was the first to answer and he gave you what you had asked for.

It's just a comment, really - in case it's useful.

Best regards to everyone following this question!