PDA

View Full Version : Select a couple of cells per row



wmr
10-21-2015, 03:15 AM
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
10-25-2015, 03:57 PM
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
10-27-2015, 06:16 AM
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
10-27-2015, 11:36 AM
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
10-28-2015, 12:08 AM
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