Creating a range that indexes in a For loop
 

For i = 2 To 20
If Cells(i, 1) = Cells(i + 1, 1) Then

Else
i = i + 1
Rows(i).Insert
' Here's where I would like to select the range for row i, column 1
through row i, column 12 and then format it with colors, borders, etc.
After that I would continue my loop. How do I write my range to be
able to index the rows since i changes each time that that the "if"
statement isn't true?

End If
Next i


Any help would be appreciated! Thank you.

Creating a range that indexes in a For loop
 

I'm not sure exactly where you wanted to use the code but here is one way to
do it.

For i = 2 To 20
If Cells(i, 1) = Cells(i + 1, 1) Then
For j = 1 To 12
Cells(i, j).Interior.ColorIndex = 3 + x
x = x + 1
Next
Else
i = i + 1
Rows(i).Insert

If the first statement is true, then the first 12 cells on that row would be
colored with colors 3 through 15 consecutively. You could set up an
algorithm instead of the for next loop to set a varying color pattern.

"Patel" wrote:


For i = 2 To 20
If Cells(i, 1) = Cells(i + 1, 1) Then

Else
i = i + 1
Rows(i).Insert
' Here's where I would like to select the range for row i, column 1
through row i, column 12 and then format it with colors, borders, etc.
After that I would continue my loop. How do I write my range to be
able to index the rows since i changes each time that that the "if"
statement isn't true?

End If
Next i


Any help would be appreciated! Thank you.

Creating a range that indexes in a For loop
 

On Aug 8, 10:15*am, JLGWhiz wrote:
I'm not sure exactly where you wanted to use the code but here is one way to
do it.

*For i = 2 To 20
* * * * *If Cells(i, 1) = Cells(i + 1, 1) Then
* * * * * * * *For j = 1 To 12
* * * * * * * * * * Cells(i, j).Interior.ColorIndex = 3 + x
* * * * * * * * * * * *x = x + 1
* * * * * * * *Next
* * * * *Else
* * * * * * *i = i + 1
* * * * * * *Rows(i).Insert

If the first statement is true, then the first 12 cells on that row would be
colored with colors 3 through 15 consecutively. *You could set up an
algorithm instead of the for next loop to set a varying color pattern.



"Patel" wrote:

* * For i = 2 To 20
* * * * If Cells(i, 1) = Cells(i + 1, 1) Then

* * * * Else
* * * * * * i = i + 1
* * * * * * Rows(i).Insert
' Here's where I would like to select the range for row i, column 1
through row i, column 12 and then format it with colors, borders, etc.
After that I would continue my loop. How do I write my range to be
able to index the rows since i changes each time that that the "if"
statement isn't true?

* * * * End If
* * Next i

Any help would be appreciated! Thank you.- Hide quoted text -

- Show quoted text -

Thank you for the idea. I was trying to get away from using another
loop and just having a dynamic range that indexed in my initial loop.
With the help of a friend, here's what we came up with:


With Range(Cells(i, 1), Cells(i, 3)) 'This selects all cells
between i,1 and i,3 with i being an integer in my loop
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.BorderAround Weight:=xlThick
End With