CODEWITHSUNDEEP
excelvba
James D Con
James D Con

Reputation: 41

Comparing two columns in a worksheet using VBA

I have two columns in a worksheet of Title Name List 1 and Name List 2.

I want to compare these two columns and highlight in red which name is not present in Name List 1 but present in Name list 2 and vice versa. If names are common in both columns then leave it as white.

The range of the columns of both list is not the same.

Sub indicators()
    lastrowlist1 = Range("C1000000").End(xlUp).Row
    lastrowlist2 = Range("K1000000").End(xlUp).Row

    For i = 5 To lastrowlist1
        Range("C" & i).Value = findval
        For j = 5 To lastrowlist2
            If Range("K" & j).Value <> Range("C" & i).Value Then
                Range("K" & j).Interior.ColorIndex = 4
                Range("C" & i).Interior.ColorIndex = 4
            Else
                Range("K" & j).Interior.ColorIndex = 2
                Range("C" & i).Interior.ColorIndex = 2
            End If
        Next j
    Next i

End Sub

Upvotes: 1

Views: 171

Answers (2)

patel
patel

Reputation: 440

With VBA, columns A,B

Sub duplicates()
    Dim i As Integer
    Dim var As Variant

    For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
      var = Application.Match(Cells(i, 1).Value, Columns(2), 0)
      If Not IsError(var) Then
         Cells(i, "A").Interior.Color = 255
      End If
    Next i
End Sub

Upvotes: 0

Alex P
Alex P

Reputation: 12497

Suppose I have the following:

    A        B
1   Peter    Peter
2   James    Sarah
3   Claire   John
4   Sandra
5   John

I can now apply conditional formatting for each column.

  1. Highlight A1:A5
  2. Conditional formatting > New Rule > Use a formula...
  3. Formula: =ISERROR(MATCH(A1,$B$1:$B$3,0))

  4. Apply format

  5. Highlight B1:B3

  6. Conditional formatting > New Rule > Use a formula...
  7. Formula: =ISERROR(MATCH(B1,$A$1:$A$5,0))
  8. Apply format

That should highlight the names you need i.e. appear in one list but not the other.

Upvotes: 1

Related Questions