how to highlights the same value with duplicates in another column

[fayez_MrExcel]

i have multiple employee with the same employee number in column A and a code in column b. how can I highlights the same value with duplicates in column B. Ex:

Col A | Col B
1001 code1
1001 code2
1001 code3
1001 code1
1002 code1
1002 code2
1003 code3
1004 code2
1004 code2

In the above example, i want to highlight emp no. 1001 with the same code1 and emp no. 1004 with same code2.

Note. i want to highlight both emp no. and the code

Thanks

 

[mumps]

Assuming your data starts in row 1, try:

Sub CompareLists()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In Range("A1", Range("A" & Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value & Rng.Offset(0, 1).Value) Then
            RngList.Add Rng.Value & Rng.Offset(0, 1).Value, Nothing
        Else
            Rng.Resize(, 2).Interior.ColorIndex = 3
        End If
    Next
    RngList.RemoveAll
    Application.ScreenUpdating = True
End Sub

 

[fayez_MrExcel]

thanks Mumps... it works but i wanted to highlighted all 1001 with code1 and 1004 with code2. Thanks

 

[Fluff]

How about this mod to mumps code

Sub CompareLists()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In Range("A1", Range("A" & Rows.Count).End(xlUp))
        If Not RngList.Exists(Rng.Value & Rng.Offset(0, 1).Value) Then
            RngList.Add Rng.Value & Rng.Offset(0, 1).Value, [COLOR=#0000ff]Rng[/COLOR]
        Else
            Rng.Resize(, 2).Interior.ColorIndex = 3
            [COLOR=#0000ff]RngList(Rng.Value & Rng.Offset(0, 1).Value).Resize(, 2).Interior.Color = vbRed[/COLOR]
        End If
    Next
    RngList.RemoveAll
    Application.ScreenUpdating = True
End Sub

 

[fayez_MrExcel]

yeah that works great thanks fluff and thanks mumps.... have a great day ahead

 

[Fluff]

Glad we could help & thanks for the feedback