Highlighting duplicates in one column based on the data from other column

[Deepk]

Hey guys,

Please see my sample data below. The data could be in any columns.

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]1[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]c[/TD]
[/TR]
</tbody>[/TABLE]
I want to have a macro that highlight (using interior color) the data in the first column (any column) based on the duplicates data from the other column (any column). The macro should work in following steps.

After selecting the data in first column I run the macro.
I enter the column number for the column having duplicate entries, in an inputbox
The macro generate the following output

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]1[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]3 red[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]4 red[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]6 red[/TD]
[TD]c[/TD]
[/TR]
</tbody>[/TABLE]

I hope my requirement is clear. Thank you in advance.

 

[Joe4]

Here is code that I think will do what you wanted back on your original conditions.
Note that if the column you are checking for duplicates is always one column to the right of the selected column, then you can get rid of the Input Box altogether. Just delete that line, and uncomment the line just below it.

Sub MyFormatMacro()

    Dim lr As Long
    Dim r As Long
    Dim col As Long
    Dim col2 As Variant
    Dim rng As Range
    
    Application.ScreenUpdating = True
    
'   Capture column of activecell
    col = ActiveCell.Column
    
'   Prompt for column to check for duplicates
    col2 = InputBox("Please enter column letter you wish to check for duplicates")
    'col2 = col + 1
    
'   Find last row in 2nd column
    lr = Cells(Rows.Count, col2).End(xlUp).Row
    
'   Loop through each row
    For r = 1 To lr
'       Set range for formula
        Set rng = Range(Cells(1, col2), Cells(r, col2))
'       Check for duplicates
        If Application.WorksheetFunction.CountIf(rng, Cells(r, col2).Value) > 1 Then
'           Color cell red
            Cells(r, col).Interior.Color = 255
        End If
    Next r

    Application.ScreenUpdating = True

End Sub

 

[Deepk]

Here is code that I think will do what you wanted back on your original conditions.
Note that if the column you are checking for duplicates is always one column to the right of the selected column, then you can get rid of the Input Box altogether. Just delete that line, and uncomment the line just below it.

Thank you Joe4. It works well. Have a good day!

 

[Joe4]

You are welcome!

 

[Deepk]

Here is code that I think will do what you wanted back on your original conditions.
Note that if the column you are checking for duplicates is always one column to the right of the selected column, then you can get rid of the Input Box altogether. Just delete that line, and uncomment the line just below it.

Sub MyFormatMacro()

    Dim lr As Long
    Dim r As Long
    Dim col As Long
    Dim col2 As Variant
    Dim rng As Range
    
    Application.ScreenUpdating = True
    
'   Capture column of activecell
    col = ActiveCell.Column
    
'   Prompt for column to check for duplicates
    col2 = InputBox("Please enter column letter you wish to check for duplicates")
    'col2 = col + 1
    
'   Find last row in 2nd column
    lr = Cells(Rows.Count, col2).End(xlUp).Row
    
'   Loop through each row
    For r = 1 To lr
'       Set range for formula
        Set rng = Range(Cells(1, col2), Cells(r, col2))
'       Check for duplicates
        If Application.WorksheetFunction.CountIf(rng, Cells(r, col2).Value) > 1 Then
'           Color cell red
            Cells(r, col).Interior.Color = 255
        End If
    Next r

    Application.ScreenUpdating = True

End Sub

hi Joe4,

Sorry to disturb you again.

I want to modify this code so the I can enter (in the inputbox) the column number of second column instead of column letter. Could you please help me with that. Thank you.

 

[Joe4]

Simply change the variable declaration to long, i.e.

    Dim col2 As Long

and you can change the wording of your InputBox to anything you like.

 

[Abhi2504]

Hey , joey i too have some doubts regarding macro in excel but i am not able to raise a thread or post it . Can you help me with that ?

 

[Joe4]

Hey , joey i too have some doubts regarding macro in excel but i am not able to raise a thread or post it . Can you help me with that ?

Looks like you were able to do it a few days ago:
https://www.mrexcel.com/forum/excel-questions/1049805-changing-color-copied-cells.htm