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.

"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.
>

On Aug 8, 10:15*am, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> 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.
>
>
>
> "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