Programmatically add borders
Posted by Pam on October 01, 2001 7:43 AM
I have a worksheet that has (for the sake of example) 6 columns and 30 rows. In the first column, I have "Apple" in the first 10 rows, "Orange" in the next 3, and "Banana" in the last 17. I would like to create a macro that will search down that first column for a change in the word, and then place a thick black border to group the Apple, Orange and Banana rows.
Posted by Juan Pablo on October 01, 2001 8:21 AM
You can do this using Conditional Formatting.
Select the entire range, in your case A1:F30 (If you have headings then select A2:F30). Now goto Format - COnditional formating, and select formula.
Type =$A2<>$A3
and in the pattern, select borders, and activate the bottom border.
If you NEED to do this in VB let me know.
Juan Pablo
Posted by Pam on October 01, 2001 9:08 AM
Unfortunately, I'm already using 3 Conditional formats for the cells and Excel only allows 3. So, I can't add one more. And yes, I do need to do this in VB. Thanks!
Posted by Juan Pablo on October 01, 2001 9:25 AM
Ok then, try something like this.
Sub Borders()
Dim F As Long
Dim C As Integer
Dim i As Long
F = Range("A65536").End(xlUp).Row
C = Range("IV2").End(xlToLeft).Column
For i = 2 To F 'Starts in Row 2 and goes to the end of file
If Cells(i, 1) <> Cells(i + 1, 1) Then
Range(Cells(i, 1), Cells(i, C)).Borders(xlEdgeBottom).Weight = xlMedium
End If
Next i
End Sub
Juan Pablo
Posted by Pam on October 01, 2001 1:01 PM
That worked! Thanks a bunch. Such quick responses... this was a great help.
