Borders around cells with data in them

[Johnnyboy5]

I would like a macro to automatically put a border around any cells in
a sheet that has data in it (numbers or text)

and cells with no data have no borders.

thanks

Johnnboy

 

[GS]

Johnnyboy5 submitted this idea :

I would like a macro to automatically put a border around any cells in
a sheet that has data in it (numbers or text)

and cells with no data have no borders.

thanks

Johnnboy

This is something I would normally use ConditionalFormatting for
because a macro would have to be fired by a sheet event, which could
seriously hurt performance if there's a lot of data. Excel, however,
can handle this much better (and more efficiently) without penalty.

To do this using CF...
1. Click the intersecting header of row/column headers at the top left
corner of the sheet you want borders placed.

2. Choose Conditional Formatting... from the Format menu.

3. Select 'Formula' from the dropdown.

4. Enter: =A1<>"" in the criteria box.

5. click the Format button and configure your border properties.

6. click OK twice.

 

[Johnnyboy5]

Johnnyboy5 submitted this idea :



This is something I would normally use ConditionalFormatting for
because a macro would have to be fired by a sheet event, which could
seriously hurt performance if there's a lot of data. Excel, however,
can handle this much better (and more efficiently) without penalty.

To do this using CF...
1.  Click the intersecting header of row/column headers at the top left
corner of the sheet you want borders placed.

2.  Choose Conditional Formatting... from the Format menu.

3.  Select 'Formula' from the dropdown.

4.  Enter:  =A1<>""  in the criteria box.

5.  click the Format button and configure your border properties.

6.  click OK twice.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks - that's an awsume solution.

job done.

5 star answer

 

[Don Guillett Excel MVP]

Sub BorderRange()
With ActiveSheet
la = .Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Address
..Range("a1:" & la).Borders.LineStyle = xlContinuous
End With
End Sub

 

[Johnnyboy5]

Sub BorderRange()
With ActiveSheet
la = .Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Address
.Range("a1:" & la).Borders.LineStyle = xlContinuous
End With
End Sub

Thanks Don for your reply -

 

[GS]

Johnnyboy5 brought next idea :

Thanks - that's an awsume solution.

job done.

5 star answer

You're very welcome!
Thanks for the feedback, ..always appreciated!