Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup using concatenated column indexes | Excel Worksheet Functions | |||
Obtain current RGB settings for color indexes 17 thru 27 | Excel Programming | |||
plz help: creating named range in VBA, loop goes haywire | Excel Programming | |||
multiple chart indexes | Excel Programming | |||
Figuring out what the Shape Indexes are. | Excel Programming |