Format Borders

[STEVEB]

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

 

[Tom Ogilvy]

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

 

[bhofsetz]

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

 

[STEVEB]

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

 

[bhofsetz]

Your syntax is off a little on this line

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

change it to

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

HT

 

[Norman Jones]

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