Conditional Borders through a Macro
 

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.

Conditional Borders through a Macro
 

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.

Conditional Borders through a Macro
 

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.

Conditional Borders through a Macro
 

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.

Conditional Borders through a Macro
 

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.

Conditional Borders through a Macro
 

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.

Conditional Borders through a Macro
 

.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.

Conditional Borders through a Macro
 

Thank you, Sam. It works great!

"Sam Wilson" wrote:

.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.

Conditional Borders through a Macro
 

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.

Conditional Borders through a Macro
 

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.

Conditional Borders through a Macro
 

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

--
Rick (MVP - Excel)


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

Conditional Borders through a Macro
 

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

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

--
Rick (MVP - Excel)


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

Conditional Borders through a Macro
 

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


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

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

--
Rick (MVP - Excel)


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

Conditional Borders through a Macro
 

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


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

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

--
Rick (MVP - Excel)


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

Conditional Borders through a Macro
 

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.

"Rick Rothstein" wrote:

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


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

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

--
Rick (MVP - Excel)


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

Conditional Borders through a Macro
 

No, your description was fine... my last post telling you to ignore this
code crossed with the message I am now responding to. I am quite hopeful
that this code will do what you want...

Sub Demo()
Dim R As Range
Dim C As Range
Dim Prev 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))
Set Prev = .Cells(LastRow + 1, "A")
For Each C In R
If C.Value < Prev.Value Then
With C.Resize(1, 27).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 9
End With
End If
Set Prev = C
Next
End With
End Sub

--
Rick (MVP - Excel)


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

"Rick Rothstein" wrote:

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


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

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

--
Rick (MVP - Excel)


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

Conditional Borders through a Macro
 

Thank you, Rick. It works perfectly. I appreciate your help and expertise,
and your patience. I look forward to figuring out why it works, too! Again,
Thanks.


"Rick Rothstein" wrote:

No, your description was fine... my last post telling you to ignore this
code crossed with the message I am now responding to. I am quite hopeful
that this code will do what you want...

Sub Demo()
Dim R As Range
Dim C As Range
Dim Prev 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))
Set Prev = .Cells(LastRow + 1, "A")
For Each C In R
If C.Value < Prev.Value Then
With C.Resize(1, 27).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 9
End With
End If
Set Prev = C
Next
End With
End Sub

--
Rick (MVP - Excel)


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

"Rick Rothstein" wrote:

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


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

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

--
Rick (MVP - Excel)


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

Conditional Borders through a Macro
 

If you have any question remaining after you try to figure out what my code
it doing, please feel free to post back here and ask.

--
Rick (MVP - Excel)


"MSchmidty2" wrote in message
...
Thank you, Rick. It works perfectly. I appreciate your help and
expertise,
and your patience. I look forward to figuring out why it works, too!
Again,
Thanks.


"Rick Rothstein" wrote:

No, your description was fine... my last post telling you to ignore this
code crossed with the message I am now responding to. I am quite hopeful
that this code will do what you want...

Sub Demo()
Dim R As Range
Dim C As Range
Dim Prev 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))
Set Prev = .Cells(LastRow + 1, "A")
For Each C In R
If C.Value < Prev.Value Then
With C.Resize(1, 27).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = 9
End With
End If
Set Prev = C
Next
End With
End Sub

--
Rick (MVP - Excel)


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

"Rick Rothstein" wrote:

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


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

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

--
Rick (MVP - Excel)


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