If i <> "" then....Compare active row to next row only

[Lovelylou79]

Hi All,

I've come to the end part of a wee project and am a little stuck. I need to compare the data in two rows and highlight both if the entire rows, with the exception of coloumn "A" are duplicates.

The code will activate if "A" has a value. That then becomes the source row, checking the row directly beneath. Once those 2 rows are compared, the code will find the next "A" with data, compare 2 rows only, then continue to lastrow.

I've begun the code by concatenating active row cells B to N for comparison and am now stuck on the For, If, then for highlighting the duplicates. My code is a bit of a mess and doesnt work at all, but I think I'm on the right track. Any help would be greatly appreciated...

Dim i As IntegerDim lr As Integer


lr = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row


Range("N2:N" & lr).Formula = "=CONCATENATE(RC[-12]&RC[-11]&RC[-10]&RC[-9]&RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1])"


Set rng1 = Cells(i).Offset(0, 13)
Set rng2 = Cells(i).Offset(1, 13)


For i = 2 To lr
    If Cells(i, 1) = "" Then
    i = i + 1
    Else
    If rng1 = rng2 Then
        rng1.EntireRow.Interior.ColorIndex = 15
        rng2.EntireRow.Interior.ColorIndex = 15
    End If
    Next i

 

[tdh777]

Hi Lou,

When you refer to cells(i) in your code before you have set i you are actually referring to Cells(0)... which is not what you want

I made a few other corrections/improvements to your code :

Try this :

...
[COLOR=#574123]Range("N2:N" & lr).Formula[B]R1C1[/B] = "=CONCATENATE...

[/COLOR]For i = 2 To lr
    If Cells(i, 1).Value > "" Then
        Set rng1 = Cells(i, 14)                    ' 14 = Column N
        Set rng2 = Cells(i + 1, 14)                ' 14 = Column N
        If rng1.Value = rng2.Value Then
            rng1.EntireRow.Interior.ColorIndex = 15
            rng2.EntireRow.Interior.ColorIndex = 15
        End If
    End If
Next i

This worked perfectly on some test data...although I must admit I am not a fan of grey !

I hope u realize you could achieve the same thing simply by :

1. Putting your formula into Row N
2. Putting an extra formula in eg Column O such as

=(N2=N1)

and
3. Put a filter on Column O to show all the TRUE values.
4. Then simply highlight all the displayed rows (the ones which are duplicates) in your chosen highlight colour.

In other words you can achieve the same result without ever having to write a line of VBA code.

Cheers.
Warren K.