I want to compare two columns and highlight the difference in yellow colour

[Gopi_Krishna1 (Gopi Krishna)]

Hi everyone, I want to compare column A and column B , want to highlight the missing things in column A using yellow colour in excel.
Suggest a method to get this done😊

[Parvathy (PS Parvathy)]

Hi @Gopi_Krishna1

Input:

Macros Code:

Sub HighlightMissingInColumnA()
    Dim ws As Worksheet
    Dim lastRowA As Long, lastRowB As Long
    Dim rngA As Range, rngB As Range
    Dim cell As Range
    Dim found As Range
    
    ' Set your worksheet here
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
    
    ' Find the last row in columns A and B
    lastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' Set the ranges for columns A and B, starting from the second row to avoid headers
    Set rngA = ws.Range("A2:A" & lastRowA)
    Set rngB = ws.Range("B2:B" & lastRowB)
    
    ' Clear any existing yellow highlights
    rngA.Interior.ColorIndex = xlNone
    
    ' Loop through each cell in column A and check if it exists in column B
    For Each cell In rngA
        Set found = rngB.Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
        If found Is Nothing Then
            ' Highlight the cell in yellow if not found in column B
            cell.Interior.Color = vbYellow
        End If
    Next cell
End Sub

Output:

WORKFLOW:

TXT FILE
HighlightMissingTHing.txt (1.1 KB)

Hope it helps!!