Creating a range that indexes in a For loop

  • Thread starter Thread starter Patel
  • Start date Start date
P

Patel

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.
 
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.
 
I'm not sure exactly where you wanted to use the code but here is one wayto
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 wouldbe
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.








- Show quoted text -
Click to expand...

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
 
Back
Top