Link to home
Start Free TrialLog in
Avatar of Escanaba
EscanabaFlag for United States of America

asked on

Excel 2007 Comparison VBA

Hello,

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.

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of ukerandi
ukerandi
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
See attached file for a solution using conditional formatting.

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

 EE2-highlight.xlsm
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
    Rows(i).Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    GoTo 10
    End If
Next
Rows(i).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
10
Next
End Sub

Open in new window

Avatar of Escanaba

ASKER

Thanks
Escanaba,

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!