how to move a selection area?

[CyberTF]

I have a macro that act's on 2 neighboring cells. I need to do this with several consecutive rows, how can i get vba to move the selection area down 1 row without actually moving the cell?

I define my original selection and call the following sub to format it...


Sub create_info()
' Create info boxes
Selection.Interior.ColorIndex = 34
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub

after this completes i need to move the selection down to the next row, same columns, and execute this code again.

CyberTF
Rochester, NY

[Jacob Hilderbrand]

You do not need to do this row by row do you? Just define the whole range you want to work with. Also you do not need to select anything.



Dim Rng As Range
Set Rng = Range("A1:C50")



Then just replace Selection with Rng in your code.

[CyberTF]

basically what i need to do is place a border around a 2x6 cell area however there cannot be a dividing border between side by side cells only between rows..

http://www.openwebtech.com/cells.gif

i hope this is enough of a demo to understand.

CyberTF
Rochester, NY

[Jacob Hilderbrand]

Ok, I see. Try something like this.


For i = 2 To n Step 7
Set Rng = Range("A" & i & ":C" & i+6)
With Rng
Rng.Interior.ColorIndex = 34
Rng.Borders(xlDiagonalDown).LineStyle = xlNone
Rng.Borders(xlDiagonalUp).LineStyle = xlNone
With Rng.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Rng.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Rng.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Rng.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Rng.Borders(xlInsideHorizontal).LineStyle = xlNone
Rng.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
Next i


n is the end for the loop. Replace that with a number or assign a value to n.

[CyberTF]

that is exactly what I am looking for. I couldn't wrap my mind around how to adjust the selection in the code and I knew it would be a loop. Been battling this all day.
Thanks a million! :beerchug:

CyberTF
Rochester, NY

[Jacob Hilderbrand]

You're Welcome :beerchug:

Take Care