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.

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

"MSchmidty2" wrote:

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, 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" wrote:

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

"MSchmidty2" wrote:

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.

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" wrote:

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" wrote:

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

"MSchmidty2" wrote:

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.

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" wrote:

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" wrote:

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" wrote:

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

"MSchmidty2" wrote:

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.

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

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" wrote:

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" wrote:

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" wrote:

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

"MSchmidty2" wrote:

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.

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

"Sam Wilson" wrote:

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" wrote:

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" wrote:

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

"MSchmidty2" wrote:

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.

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 (MVP - Excel)


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

"Sam Wilson" wrote:

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" wrote:

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" wrote:

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

"MSchmidty2" wrote:

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.

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" wrote in message
...
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.