Format Borders
 

Does Anyone have suggestions for adding borders to a range of cells tha
vary each day?

My current spreadsheet adds borders to Columns A:C including cells tha
don't have any data. Is there a way to add borders to only the row
that have cells with data? Also, is there a way to highlight the od
number rows with data?

Thank

--
STEVE
-----------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...nfo&userid=187
View this thread: http://www.excelforum.com/showthread.php?threadid=39099

Format Borders
 

Possibly (it depends on what you mean by contains data):

Dim rng as Range, rng1 as Range, rng2 as Range
On Error resume Next
set rng1 = Columns("A:C").specialCells(xlFormulas)
set rng2 = Columns("A:C").specialCells(xlConstants)
On Error goto 0
if not rng1 is nothing then
if not rng2 is nothing then
set rng = Union(rng1,rng2)
else
set rng = rng1
end if
Else if not rng2 is nothing then
set rng = rng2
End if

If not rng is nothing then
set rng = Intersect(Columns("A:C"),rng.Entirerow)
' now put your borders to rng
end if

--
Regards,
Tom Ogilvy

"STEVEB" wrote in
message ...

Does Anyone have suggestions for adding borders to a range of cells that
vary each day?

My current spreadsheet adds borders to Columns A:C including cells that
don't have any data. Is there a way to add borders to only the rows
that have cells with data? Also, is there a way to highlight the odd
number rows with data?

Thanks


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=390997

Format Borders
 

Why not count the rows with data using

NumRows = Cells(Rows.Count, "C").End(xlUp)

then add borders to your range from A1:C & NumRows

To highlight the rows with data you could simply loop through the rows

From 1 to NumRows and increment by 2 each loop.

HTH


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=390997

Format Borders
 

bhofsetz,

Thanks for your help!

When I input the code:

NumRows = Cells(Rows.Count, "C").End(xlUp)
Range("A1:C & NumRows").Select

I get this error:

Run-time error '1004':

Method 'Range' of object '_Global' failed

Am I doing something wrong?

Thanks again


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=390997

Format Borders
 

Your syntax is off a little on this line

Range("A1:C & NumRows").Select

change it to

Range("A1:C" & NumRows).Select

HT

--
bhofset
-----------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880
View this thread: http://www.excelforum.com/showthread.php?threadid=39099

Format Borders
 

Hi Steve,

In addition to bhodsetz's response, change:

NumRows = Cells(Rows.Count, "C").End(xlUp)

to:

NumRows = Cells(Rows.Count, "C").End(xlUp).Row


---
Regards,
Norman



"STEVEB" wrote in
message ...

bhofsetz,

Thanks for your help!

When I input the code:

NumRows = Cells(Rows.Count, "C").End(xlUp)
Range("A1:C & NumRows").Select

I get this error:

Run-time error '1004':

Method 'Range' of object '_Global' failed

Am I doing something wrong?

Thanks again


--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile:
http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=390997