Select a couple of cells per row

[wmr]

Hello,

I want to check whether a name exist more than ones in one row.
Then a want to check the next row. And so one.

I can use Range("D2, F2, H2, J2, L2"). But that is not flexible. I want to use a for next loop.

In the file you will find my code.

Do one of you know how I can fix this problem?

Kind regards,
Willem

[Leith Ross]

Hello wmr,

This version of the macro should be easier to understand and modify. It will add the Duplicates Conditional Formatting to column D, F, H, and J starting with row 2 down to the last row in column "A".

Sub AddCondFormat()

    Dim i       As Long
    Dim LastRow As Long
    Dim Rng     As Range
    Dim Wks     As Worksheet
    
        Set Wks = Worksheets("Duplicate Names")
        Set Rng = Range("D2, F2, H2, J2, L2")

        LastRow = Wks.Cells(Rows.Count, "A").End(xlUp).Row
        If LastRow < Rng.Row Then Exit Sub
        
        For i = 0 To LastRow - Rng.Row
            For Each Cell In Rng.Offset(i, 0)
                With Cell
                    .FormatConditions.Delete
                    With .FormatConditions
                        .Add xlUniqueValues
                        .Item(1).ModifyAppliesToRange Rng.Offset(i, 0)
                        .Item(1).DupeUnique = xlDuplicate
                        .Item(1).Font.Color = -16383844
                        .Item(1).Font.TintAndShade = 0
                        .Item(1).Interior.PatternColorIndex = xlAutomatic
                        .Item(1).Interior.Color = 13551615
                        .Item(1).Interior.TintAndShade = 0
                        .Item(1).StopIfTrue = False
                    End With
                End With
            Next Cell
        Next i

End Sub

[wmr]

Hello Leith,

Thank you for your answer.

Your macro only works for John. In the second line their is twice the name of Susan.

I am looking for a solution so that I can use i in a for next loop.

So
"D" & i
"F" & i
"H" & i
"J" & i
"L" & i

Kind regards
Willem

[Leith Ross]

Hello wmr,

You probably meant to enter "Susan" twice. In your example workbook, you entered "Susan" and "Suzan" which are two different names.

The way the loop is written it does step through the cells rows by using "i". The loop starts at zero instead of one because "i" is now the row offset to our starting Range of cells "D2", "F2", "H2", "J2", and "L2". The second loop "For Each Cell", applies the same conditional formatting to each cell in the Range("D2", "F2", "H2", "J2", "L2").Offset(i, 0). It is important to remember that "i" is a relative row reference within this range itself and not an absolute reference to the row on the worksheet. The method is a bit different than what you wanted to do but accomplishes the same goal.

You can do it your way like this...

Sub Macro1()

    Dim i       As Long
    Dim LastRow As Long
    Dim Rng     As Range
    Dim Wks     As Worksheet
    
        Set Wks = Worksheets("Duplicate Names")

        LastRow = Wks.Cells(Rows.Count, "A").End(xlUp).Row
        If LastRow < 2 Then Exit Sub

        For i = 2 To LastRow
            Call AddCF(Wks.Range("D" & i))
            Call AddCF(Wks.Range("F" & i))
            Call AddCF(Wks.Range("H" & i))
            Call AddCF(Wks.Range("J" & i))
            Call AddCF(Wks.Range("L" & i))
        Next i
        
End Sub

Sub AddCF(ByRef Cell As Range)

        With Cell
            .FormatConditions.Delete
            With .FormatConditions
                .Add xlUniqueValues
                .Item(1).ModifyAppliesToRange Rng.Offset(i, 0)
                .Item(1).DupeUnique = xlDuplicate
                .Item(1).Font.Color = -16383844
                .Item(1).Font.TintAndShade = 0
                .Item(1).Interior.PatternColorIndex = xlAutomatic
                .Item(1).Interior.Color = 13551615
                .Item(1).Interior.TintAndShade = 0
                .Item(1).StopIfTrue = False
            End With
        End With
   
End Sub

[wmr]

Hello Leith,

Thanks again!

Excecuting this line: .Item(1).ModifyAppliesToRange Rng.Offset(i, 0) the message "Object required" appears.

I do not understand what the problem is.

Kind regards,
Willem