Column entry search and highlight issue

[amitnewbee123]

Hi All,

In the attached excel 'students_test.xls' , I have 3 columns students,marks,school in the tab named 'Details' and in the tab Students I have students column.

I want to write some VB script or some excel formula(which ever approach) to search the students name present in the tab name Students against the enteries for Students column present in the tab Details and if there is a match then mark it in red color in the students column present in the tab Details.

Check all these values in an iteration column by column entry in Students tab .

For example : search Amit entry present in the Students column of the students tab against the entries of the Students column in the Details tab and if a match is found please mark it as red color . Please do it for all the enteries present in the Students tab (students column) against those present in the Details tab(Students column)

Please guide me how to achieve this concept, I tried VLOOKUP but I got struck, conditional formatting also didn't help, I am new in these scripting stuffs so if you can show me a demonstration for the below sample data it will be of great help.
Kind regards
Amit

 

[Logit]

Option Explicit


Sub CompareColumnsAndColor()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rng1 As Range, rng2 As Range
    Dim cell1 As Range, cell2 As Range
    Dim matchFound As Boolean
    
    ' Set worksheets
    Set ws1 = ThisWorkbook.Sheets("Details")
    Set ws2 = ThisWorkbook.Sheets("Students")
    
    ' Set ranges starting from row 2 to skip headers
    Set rng1 = ws1.Range("A2", ws1.Cells(ws1.Rows.Count, "A").End(xlUp))
    Set rng2 = ws2.Range("A2", ws2.Cells(ws2.Rows.Count, "A").End(xlUp))
    
    ' Loop through each cell in Sheet1 column A starting from row 2
    For Each cell1 In rng1
        matchFound = False
        
        ' Check each cell in Sheet2 column A for a match
        For Each cell2 In rng2
            If cell1.Value = cell2.Value And Not IsEmpty(cell1.Value) Then
                matchFound = True
                Exit For
            End If
        Next cell2
        
        ' If match found, color the cell in Sheet1
        If matchFound Then
            cell1.Interior.Color = RGB(255, 255, 0) ' Yellow
        End If
    Next cell1
    
    'MsgBox "Comparison complete! Matching names in Sheet1 (starting from row 2) have been highlighted."
End Sub

 

[AlanSidman]

An alternative with Power Query

See attached.

 

[amitnewbee123]

i tried the above but it doesnt work with consisteny is there a way we can add a flag as 1 for match in details tab say by naming it as Flag column?I dont know why is it not working.Please help

 

[p45cal]

In D2 of Details sheet (also in D1 add the header, whatever you want (Flag?)):
=IF(ISNUMBER(MATCH(A2,Students!$A$2:$A$4,0)),1,"")
copy down.