Hi,

I have a piece of code that adds a new line into an XL page that I'm using
as a database.

When the line is added, I want Xl to format it so it looks pretty for the
users, 'cos that's important to them :-)

What I'm struggling with is adding a border to the various cells that have
been added.

The code I am using is:

With Sheets("Identification").Range("a" & rRiskCount & ":n" &
rRiskCount) ' top left justify all cells
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.Borders.LineStyle = xlContinuous ' add borders
End With

(rRiskCount is defined earlier and returns an integer)

Which works except that it is putting a border around every cell in cloumns
A through N rather than just those in the range defined as A & rRiskCount
through N rRiskCount.

How do I limit the formatting monster?

TIA

Dave

I just entered your code on a worksheet and it seemed to run fine. I see in
your question that the first line of code wraps around. This may be just
from the formatting on this screen but make sure if it does wrap in your code
that you have a "_" at the end of the first line. Other than that, it seems
to work.

"Risky Dave" wrote:

Hi,

I have a piece of code that adds a new line into an XL page that I'm using
as a database.

When the line is added, I want Xl to format it so it looks pretty for the
users, 'cos that's important to them :-)

What I'm struggling with is adding a border to the various cells that have
been added.

The code I am using is:

With Sheets("Identification").Range("a" & rRiskCount & ":n" &
rRiskCount) ' top left justify all cells
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.Borders.LineStyle = xlContinuous ' add borders
End With

(rRiskCount is defined earlier and returns an integer)

Which works except that it is putting a border around every cell in cloumns
A through N rather than just those in the range defined as A & rRiskCount
through N rRiskCount.

How do I limit the formatting monster?

TIA

Dave

Your code works fine for me. If I define rRiskCount to be 4, then the only
cells your code puts borders around are all the cell in the range A4:N4 and
no other cells.

--
Rick (MVP - Excel)


"Risky Dave" wrote in message
...
Hi,

I have a piece of code that adds a new line into an XL page that I'm using
as a database.

When the line is added, I want Xl to format it so it looks pretty for the
users, 'cos that's important to them :-)

What I'm struggling with is adding a border to the various cells that have
been added.

The code I am using is:

With Sheets("Identification").Range("a" & rRiskCount & ":n" &
rRiskCount) ' top left justify all cells
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.Borders.LineStyle = xlContinuous ' add borders
End With

(rRiskCount is defined earlier and returns an integer)

Which works except that it is putting a border around every cell in
cloumns
A through N rather than just those in the range defined as A & rRiskCount
through N rRiskCount.

How do I limit the formatting monster?

TIA

Dave

Both,

thanks for your responses. there must be something else happening then, 'cos
it is definitely putting borders around every line in my sheet.

Mmmmm

Dave

"Rick Rothstein" wrote:

Your code works fine for me. If I define rRiskCount to be 4, then the only
cells your code puts borders around are all the cell in the range A4:N4 and
no other cells.

--
Rick (MVP - Excel)


"Risky Dave" wrote in message
...
Hi,

I have a piece of code that adds a new line into an XL page that I'm using
as a database.

When the line is added, I want Xl to format it so it looks pretty for the
users, 'cos that's important to them :-)

What I'm struggling with is adding a border to the various cells that have
been added.

The code I am using is:

With Sheets("Identification").Range("a" & rRiskCount & ":n" &
rRiskCount) ' top left justify all cells
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.Borders.LineStyle = xlContinuous ' add borders
End With

(rRiskCount is defined earlier and returns an integer)

Which works except that it is putting a border around every cell in
cloumns
A through N rather than just those in the range defined as A & rRiskCount
through N rRiskCount.

How do I limit the formatting monster?

TIA

Dave

Try commenting out the line that inserts the borders and see what happens
when you run the code...
--
HTH...

Jim Thomlinson


"Risky Dave" wrote:

Both,

thanks for your responses. there must be something else happening then, 'cos
it is definitely putting borders around every line in my sheet.

Mmmmm

Dave

"Rick Rothstein" wrote:

Your code works fine for me. If I define rRiskCount to be 4, then the only
cells your code puts borders around are all the cell in the range A4:N4 and
no other cells.

--
Rick (MVP - Excel)


"Risky Dave" wrote in message
...
Hi,

I have a piece of code that adds a new line into an XL page that I'm using
as a database.

When the line is added, I want Xl to format it so it looks pretty for the
users, 'cos that's important to them :-)

What I'm struggling with is adding a border to the various cells that have
been added.

The code I am using is:

With Sheets("Identification").Range("a" & rRiskCount & ":n" &
rRiskCount) ' top left justify all cells
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.Borders.LineStyle = xlContinuous ' add borders
End With

(rRiskCount is defined earlier and returns an integer)

Which works except that it is putting a border around every cell in
cloumns
A through N rather than just those in the range defined as A & rRiskCount
through N rRiskCount.

How do I limit the formatting monster?

TIA

Dave

All the way down to the bottom of the sheet? Or only to the last data line
you entered? If the latter, is it possible that you are simply seeing the
borders from the previously added data (assuming you don't have code to
remove the previously placed borders)?

--
Rick (MVP - Excel)


"Risky Dave" wrote in message
...
Both,

thanks for your responses. there must be something else happening then,
'cos
it is definitely putting borders around every line in my sheet.

Mmmmm

Dave

"Rick Rothstein" wrote:

Your code works fine for me. If I define rRiskCount to be 4, then the
only
cells your code puts borders around are all the cell in the range A4:N4
and
no other cells.

--
Rick (MVP - Excel)


"Risky Dave" wrote in message
...
Hi,

I have a piece of code that adds a new line into an XL page that I'm
using
as a database.

When the line is added, I want Xl to format it so it looks pretty for
the
users, 'cos that's important to them :-)

What I'm struggling with is adding a border to the various cells that
have
been added.

The code I am using is:

With Sheets("Identification").Range("a" & rRiskCount & ":n"
&
rRiskCount) ' top left justify all cells
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.Borders.LineStyle = xlContinuous ' add borders
End With

(rRiskCount is defined earlier and returns an integer)

Which works except that it is putting a border around every cell in
cloumns
A through N rather than just those in the range defined as A &
rRiskCount
through N rRiskCount.

How do I limit the formatting monster?

TIA

Dave

Jim,

If I comment out the .Borders.LineStyle = xlContinuous line, no borders are
put in anywhere

Rick,

The borders are added to the end of the page (line 1048756 - this is Office
07 under Vista).

Any further suggestoins would be appreciated.

Dave

"Rick Rothstein" wrote:

All the way down to the bottom of the sheet? Or only to the last data line
you entered? If the latter, is it possible that you are simply seeing the
borders from the previously added data (assuming you don't have code to
remove the previously placed borders)?

--
Rick (MVP - Excel)


"Risky Dave" wrote in message
...
Both,

thanks for your responses. there must be something else happening then,
'cos
it is definitely putting borders around every line in my sheet.

Mmmmm

Dave

"Rick Rothstein" wrote:

Your code works fine for me. If I define rRiskCount to be 4, then the
only
cells your code puts borders around are all the cell in the range A4:N4
and
no other cells.

--
Rick (MVP - Excel)


"Risky Dave" wrote in message
...
Hi,

I have a piece of code that adds a new line into an XL page that I'm
using
as a database.

When the line is added, I want Xl to format it so it looks pretty for
the
users, 'cos that's important to them :-)

What I'm struggling with is adding a border to the various cells that
have
been added.

The code I am using is:

With Sheets("Identification").Range("a" & rRiskCount & ":n"
&
rRiskCount) ' top left justify all cells
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.Borders.LineStyle = xlContinuous ' add borders
End With

(rRiskCount is defined earlier and returns an integer)

Which works except that it is putting a border around every cell in
cloumns
A through N rather than just those in the range defined as A &
rRiskCount
through N rRiskCount.

How do I limit the formatting monster?

TIA

Dave

You mentioned that "rRiskCount is defined earlier and returns an integer".
Integers max out at 32k. Try switching them to type long if they are not
already...
--
HTH...

Jim Thomlinson


"Risky Dave" wrote:

Jim,

If I comment out the .Borders.LineStyle = xlContinuous line, no borders are
put in anywhere

Rick,

The borders are added to the end of the page (line 1048756 - this is Office
07 under Vista).

Any further suggestoins would be appreciated.

Dave

"Rick Rothstein" wrote:

All the way down to the bottom of the sheet? Or only to the last data line
you entered? If the latter, is it possible that you are simply seeing the
borders from the previously added data (assuming you don't have code to
remove the previously placed borders)?

--
Rick (MVP - Excel)


"Risky Dave" wrote in message
...
Both,

thanks for your responses. there must be something else happening then,
'cos
it is definitely putting borders around every line in my sheet.

Mmmmm

Dave

"Rick Rothstein" wrote:

Your code works fine for me. If I define rRiskCount to be 4, then the
only
cells your code puts borders around are all the cell in the range A4:N4
and
no other cells.

--
Rick (MVP - Excel)


"Risky Dave" wrote in message
...
Hi,

I have a piece of code that adds a new line into an XL page that I'm
using
as a database.

When the line is added, I want Xl to format it so it looks pretty for
the
users, 'cos that's important to them :-)

What I'm struggling with is adding a border to the various cells that
have
been added.

The code I am using is:

With Sheets("Identification").Range("a" & rRiskCount & ":n"
&
rRiskCount) ' top left justify all cells
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.Borders.LineStyle = xlContinuous ' add borders
End With

(rRiskCount is defined earlier and returns an integer)

Which works except that it is putting a border around every cell in
cloumns
A through N rather than just those in the range defined as A &
rRiskCount
through N rRiskCount.

How do I limit the formatting monster?

TIA

Dave

I just tried your code out in my copy of XL2007 using rRiskCount = 1048576
(instead of the your mistyped 1048756 value) and it worked as expected...
only the cells in A1048576:N1048576 have borders on them. Do you have any
other code running? If so, can you show it to us?

--
Rick (MVP - Excel)


"Risky Dave" wrote in message
...
Jim,

If I comment out the .Borders.LineStyle = xlContinuous line, no borders
are
put in anywhere

Rick,

The borders are added to the end of the page (line 1048756 - this is
Office
07 under Vista).

Any further suggestoins would be appreciated.

Dave

"Rick Rothstein" wrote:

All the way down to the bottom of the sheet? Or only to the last data
line
you entered? If the latter, is it possible that you are simply seeing the
borders from the previously added data (assuming you don't have code to
remove the previously placed borders)?

--
Rick (MVP - Excel)


"Risky Dave" wrote in message
...
Both,

thanks for your responses. there must be something else happening then,
'cos
it is definitely putting borders around every line in my sheet.

Mmmmm

Dave

"Rick Rothstein" wrote:

Your code works fine for me. If I define rRiskCount to be 4, then the
only
cells your code puts borders around are all the cell in the range
A4:N4
and
no other cells.

--
Rick (MVP - Excel)


"Risky Dave" wrote in message
...
Hi,

I have a piece of code that adds a new line into an XL page that I'm
using
as a database.

When the line is added, I want Xl to format it so it looks pretty
for
the
users, 'cos that's important to them :-)

What I'm struggling with is adding a border to the various cells
that
have
been added.

The code I am using is:

With Sheets("Identification").Range("a" & rRiskCount &
":n"
&
rRiskCount) ' top left justify all cells
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.Borders.LineStyle = xlContinuous ' add borders
End With

(rRiskCount is defined earlier and returns an integer)

Which works except that it is putting a border around every cell in
cloumns
A through N rather than just those in the range defined as A &
rRiskCount
through N rRiskCount.

How do I limit the formatting monster?

TIA

Dave