Conditional Borders through a Macro

[MSchmidty2]

I'm fairly new to VBA programming and using Excel 2003. I'm interested in
learning how to make a Macro that creates a number of thick borders across
columns "A" to "AA". These borders will divide lines of information from one
job number to the next, but the job numbers have a random number of
operations assigned to them, each with it's own row.
Such as: M1234 - Operation A <next row> M1234 -Operation B <next row>
M1234 - Operation C <next row> M1235 Operation A and so on. I am already
using some conditional formatting in a few of the columns of the worksheet,
so a macro seems my best option. The worksheet is about 1300 rows long, but
that is also random, so I'm looking to automatically adjust to the length.
Any tips will be appreciated. Thanks.

 

[Sam Wilson]

Sub Demo()

dim i as integer
with range("a2")
do until isempty(.offset(i,0))
if not .offset(i-1,0).value = .offset(i,0).value then
.offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
end if
i=i+1
loop
end with

End Sub

 

[Rick Rothstein \(MVP - VB\)]

Not sure exactly what you have in mind by "across columns"... do you want
the range surrounded by a border all around or do you just want lines across
the bottom of the range? To surround the range with a border, you could do
this...

Range("A5:AA8").Cells.BorderAround Weight:=xlThick

although you should look up BorderAround in the help files as there are two
other optional arguments available and several options available for each
argument. If you just want to draw a horizontal line at the bottom of the
range (sort of as a separator between sections, you could do this...

Range("A5:AA8").Cells.Borders(xlEdgeBottom).Weight=xlThick

and, again, you should look up the Borders property to see the options
available to you for it.

Rick

 

[MSchmidty2]

Sam, Thank you. this is what I was looking for. Two questions: 1. How can
I change the color of the line to dark red? And secondly, is there a way to
compensate for hidden rows? I'm using other control toolbox button macros to
display certain information within the same job number. Again, thanks.

 

[Sam Wilson]

Colour is easy - After the line:

..offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick

add this line
..offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Colorindex = 9


Not quite sure what you mean about the hidden rows?

 

[MSchmidty2]

When the macro you provided has been run and rows are then hidden, the thick
borders are hidden with them if they fall in the first or last row of a job
number. I'm wondering if a macro can 'ignore' hidden rows and apply only to
the rows on screen. I appreciate your assistance.

 

[Sam Wilson]

.offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
.offset(i-1,0).Resize(1, 27).Borders(xlEdgebottom).Weight = xlThick
.offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Colorindex = 9
.offset(i-1,0).Resize(1, 27).Borders(xlEdgebottom).Colorindex = 9

This will colour the top of the second row and the bottom of the top row, so
if either is visible it will show up.

Sam

 

[MSchmidty2]

Thank you, Sam. It works great!

.offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Weight = xlThick
.offset(i-1,0).Resize(1, 27).Borders(xlEdgebottom).Weight = xlThick
.offset(i,0).Resize(1, 27).Borders(xlEdgeTop).Colorindex = 9
.offset(i-1,0).Resize(1, 27).Borders(xlEdgebottom).Colorindex = 9

This will colour the top of the second row and the bottom of the top row, so
if either is visible it will show up.

Sam

 

[MSchmidty2]

I am also interested in making sure all of the cells on the worksheet with
information in them have thin borders by using a click button macro:

Private Sub CommandButton5_Click()
Set Range11 = ActiveSheet.Range(Range("A3"), Range("AA65536").End(xlUp))
Range11.Borders.Weight = xlThin
End Sub

The macro only changes borders until row 1092. I'm guessing there is a
limit, but I can't figure out how to get around it. Any tips?

 

[Rick Rothstein \(MVP - VB\)]

I'll bet the reason it stops at Row 1092 is because the last piece of data
in Column AA is located at Row 1092. You put the End(xlUP) evaluation on the
last cell in Column AA, so that where it will look up from, no matter how
the rest of your data is distributed. Try this macro (it uses a different
approach) and see if it works for you...

Private Sub CommandButton5_Click()
With Worksheets("Sheet1").UsedRange
.SpecialCells(xlConstants).Borders.Weight = xlThin
.SpecialCells(xlCellTypeFormulas).Borders.Weight = xlThin
End With
End Sub

Rick

 

[MSchmidty2]

Rick, Thanks for the response. You are correct in your assessment that the
data entered in column "AA" ended at row 1092. However, trying your new
approach gave me a "run time error '9' Subscript out of range" message box.

 

[Rick Rothstein \(MVP - VB\)]

I used an example worksheet reference of "Sheet1"... this needs to be
changed to the worksheet name your data is on... either that or use
ActiveSheet for the worksheet reference as you did in your code. Here is my
code with the reference changed to ActiveSheet...

Private Sub CommandButton5_Click()
With ActiveSheet.UsedRange
On Error Resume Next
.SpecialCells(xlConstants).Borders.Weight = xlThin
.SpecialCells(xlCellTypeFormulas).Borders.Weight = xlThin
End With
End Sub

Note that I added an On Error Resume Next statement to the code... this will
stop any error generated if there are no constants or no formulas on the
worksheet.

Rick

 

[MSchmidty2]

Thanks for the explanation, Rick. The only issue I had was that cells that
were empty didn't get a border around them, but I was able to work with your
instruction to get it working the way I wanted to.

 

[MSchmidty2]

Revisiting this issue, I would like to learn how to program the code below to
ignore hidden rows, if possible. I appreciate any help

 

[Rick Rothstein]

I would not have used a loop initially; rather, I would have built a range
from the StartCell to the LastRow and then applied the Borders properties to
that. Doing it that way, then adding the SpecialCells condition for visible
cells is easy...

Sub Demo()
Dim R As Range
Dim StartRow As Long
Dim LastRow As Long
StartRow = 2
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set R = Worksheets("Sheet1").Range("A" & StartRow & ":A" & LastRow). _
Resize(LastRow - 1, 27).SpecialCells(xlCellTypeVisible)
With R.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 9
End With
With R.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 9
End With
End Sub

 

[Rick Rothstein]

This would probably be considered a little "cleaner"...

Sub Demo()
Dim R As Range
Dim StartRow As Long
Dim LastRow As Long
Dim BorderStyle As Variant
StartRow = 2
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set R = Worksheets("Sheet1").Range("A" & StartRow & ":A" & LastRow). _
Resize(LastRow - 1, 27).SpecialCells(xlCellTypeVisible)
For Each BorderStyle In Array(xlEdgeTop, xlInsideHorizontal)
With R.Borders(BorderStyle)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 9
End With
Next
End Sub

 

[MSchmidty2]

Rick,
The original intent was to create a macro that divides lines of information
from one
job number to the next. The job numbers have a random number of
operations assigned to them, each with it's own row.
Such as: M1234 - Operation A <next row> M1234 -Operation B <next row>
M1234 - Operation C <next row> M1235 Operation A and so on. The problem I
ran into was that if hidden rows were the first or last line of a job number,
the border would be hidden as well. The loop in the example accomplished
that, but I am interested in learning how to do it differently, similar to
what you responded with.

 

[Rick Rothstein]

Sorry, I read the question too fast and used a horrible set of sample data.
This should do what you want...

Sub Demo()
Dim R As Range
Dim C As Range
Dim X As Integer
Dim LastRow As Long
Dim StartRow As Long
StartRow = 2
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set R = Intersect(.Rows().SpecialCells(xlCellTypeVisible), _
.Range("A" & StartRow & ":A" & LastRow))
For X = 1 To R.Areas.Count
With R.Areas(X).Resize(1, 27).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 9
End With
Next
End With
End Sub

 

[Rick Rothstein]

Nope! This is not the solution to your question either... again, a badly
chosen data set. Let me try again and I'll be back to this thread with what
I hope is a correct solution.

 

[MSchmidty2]

I'm sorry Rick, I've not been clear enough with my description. I don't need
all lines that have been hidden to show a border. I need lines that happen
to be hidden because of some other condition to be ignored. My worksheet is
rather lengthy, and the original macro swept through it comparing each cell
in column A to the one above it. If the cell content (a job number)
differed, a border would be drawn across the worksheet for organizational
purposes.