Highlight Duplicate Rows (not cells)

Question

I need to highlight duplicate rows in my Excel 2013 sheet. I can highlight duplicate cell values within rows, using "Highlight --> Duplicate" method, but what i'm trying to do is to highlight the rows as a whole that are duplicates. If only one cell is different for that row then it's not considered duplicate in my case.

Example:

Name    Task     Phone Number
A       Do 1     12345
B       Do 1     12345
A       Do 2     12345
B       Do 1     12345

For the above table, only rows 2 and 4 should be highlighted.

score 0 · Accepted Answer · edited Mar 20 '17 at 10:17

You ask to highlight the rows but I'm guessing this may be problematic (although you don't describe why you want this). Assuming you have 10 rows, and many of those match other rows, your worksheet will look like

The issue is you have no idea which row matches what row!

You will also have the issue when you next run the script, the highlight will persist.

As such, I offer a solution with an extra column of information (screen shot below):

Sub WalkThePlank()

Dim startRow As Integer
startRow = 2                 'Update this to lead us to the treasure

Dim row As Integer
row = startRow

Do While (Range("A" & row).Value <> "")

    Dim innerRow As Integer
    innerRow = row + 1

    Dim name As String
    Dim task As String
    Dim phone As String

    name = Range("A" & row).Value
    task = Range("B" & row).Value
    phone = Range("C" & row).Value

    Do While (Range("A" & innerRow).Value <> "")


        If (Range("A" & innerRow).Value = name And Range("B" & innerRow).Value = task And Range("C" & innerRow).Value = phone) Then
            Range("D" & row).Value = Range("D" & row).Value & innerRow & ", "
            Range("D" & innerRow).Value = Range("D" & innerRow).Value & row & ", "
            'Rows(row).Interior.ColorIndex = 6      'UNCOMMENT THIS LINE IF YOU WANT HIGHLIGHTING

            'Rows(innerRow).Interior.ColorIndex = 6      'UNCOMMENT THIS LINE IF YOU WANT HIGHLIGHTING
        End If

    innerRow = innerRow + 1
    Loop

    row = row + 1
Loop

End Sub

This is what it does

Do this on a copy of your file - there is no undo feature!

Note in the code there are 2 lines showing you which line to uncomment (remove the first ' on those lines and they will highlight)

How do I add VBA in MS Office?

Highlight Duplicate Rows (not cells)

1 Answers1