macro that highlights rows that do not exist in an other worksheet

0
votes

I have one file with two worksheets, both are full of names and addresses. I need a macro that will highlight rows in the first sheet if the cell A of that row does not match any rows from column A of the second sheet.

So if the first cell in a row has no matching data in any of the data in column A of sheet2 then that row is highlighted red.

Also I might want to expand this in the future so could I also specify that Sheet1 can be the active sheet, but sheet2 is called by the sheet name?

4
vbaexcel
Does it have to be a macro. This could be done easily with a formula and conditional formatting. It would also be applied automatically that way as apposed to a macro which would have to be run (though it could be tied to an event in order to run automatically). – Bafsky
I agree with @SomeSillyName. Put a VLOOKUP or INDEX/MATCH formula in the worksheet and use conditional formatting if the result shows that Column A isn't matched. – Our Man in Bananas
there's no need for a macro at all. follow @philip 's idea – Alex Gordon

4 Answers

1
votes

Try below code :

Sub Sample()

    Dim lastRow As Integer
    Dim rng As Range
    lastRow = Sheets("Sheet1").Range("A65000").End(xlUp).Row

    For i = 1 To lastRow
        Set rng = Sheets("sheet2").Range("A:A").Find(Sheets("Sheet1").Cells(i, 1))
        If rng Is Nothing Then
            Sheets("Sheet1").Cells(i, 1).EntireRow.Interior.Color = vbRed
        End If
    Next
End Sub
0
votes

Here's an ugly brute-force approach:

Dim r As Range
Dim s As Range

For Each r In ActiveSheet.UsedRange.Rows
  For Each s In Sheets("Sheet2").UsedRange.Rows
    If r.Cells(1, 1).Value = s.Cells(1, 1).Value Then
      r.Interior.ColorIndex = 3
    End If
  Next s
Next r

Here's a slicker way:

Dim r As Range
Dim s As Range

Set s = Sheets("Sheet2").Columns(1)

For Each r In ActiveSheet.UsedRange.Rows
  If Not (s.Find(r.Cells(1, 1).Value) Is Nothing) Then
    r.Interior.ColorIndex = 3
  End If
Next r
0
votes

how about this:

Sub CondFormatting()


Range("D1:D" & Range("A1").End(xlDown).Row).Formula = "=IF(ISERROR(VLOOKUP(A:A,Sheet2!A:A,1,FALSE)),""NOT FOUND"",VLOOKUP(A:A,Sheet2!A:A,1,FALSE))"

With Columns("D:D")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""NOT FOUND"""
    .FormatConditions(1).Interior.ColorIndex = 3
End With

    Range("I16").Select
End Sub
0
votes

here is an approach using a Worksheet formula:

=IF(ISERROR(VLOOKUP(A:A,Sheet2!A:A,1,FALSE)),"NOT FOUND",VLOOKUP(A:A,Sheet2!A:A,1,FALSE))

then you would use Conditional formatting to turn the cells red if column A doesn't find a match!

HTH

Philip