Creating a formatting macro in Excel 97 1

[wyld]

I create a number of Excel spreadsheets then go in and manually format them to make them 'pretty' for the users.

The spreadsheet size varies.

I want to create a macro that will do the equivalent:
- select all the cells on the active sheet
(as if I was in A1 and did ctrl-shift-end)
- create a thin line border around the highlighted cells
- select the cells in the first row
(as if I was in A1 and did ctrl-shift-arrow)
- bold the highlighted cells
- create a bold line border around the highlighted cells

I tried using ctrl-A, but that prints empty cells.

Any suggestions?

Thanks in advance.

 

[dsi]

Your best bet is to select Tools>Macro>Record, then perform each of the steps you listed. Hit the Stop button and check out the code.

 

[wyld]

I tried that, but I keep running into the problem of different sheet sizes. Ctrl-A doesn't work. Is there a way to specify the last occupied cell?

 

[dsi]

Have you tried using Ctrl-Shift-Right (or -End) instead of Ctrl-A?

 

[DaleWatson123321]

Hi wyld,

I created a sample file to perform the tasks you described, and the following code works, according to your description.

If you have any difficulty with the code, don't hesitiate to email me and I will return the Excel file to you.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca


=============
START OF CODE
=============

Dim endcell As String
Dim entire_range As String
Dim all_data As Range
Dim endcolumn As String
Dim toprow As String


Sub Format_Start()
Set_Last_Row
Set_Ranges
Format_Range
Format_TopRow
End Sub

Sub Set_Last_Row() 'determines last cell address and last column used
ActiveCell.SpecialCells(xlLastCell).Select
endcell = ActiveCell.Address 'identifies last cell address in use
ActiveCell.Offset(0, 1).Select
Selection.End(xlUp).Select
ActiveCell.Offset(0, -1).Select
endcolumn = ActiveCell.Address 'identifies last column address address (row 1)
End Sub

Sub Set_Ranges() 'creates Range Names for the entire range, and top row
entire_range = "A1" & ":" & endcell
Range(entire_range).Name = "all_data"
toprow = "A1" & ":" & endcolumn
Range(toprow).Name = "top_row"
End Sub

Sub Format_Range() 'formats the entire range
Range("all_data&quot.Select

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With

With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
End With

End Sub

Sub Format_TopRow() 'formats the top row
Range("top_row&quot.Select
Selection.Font.Bold = True

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

End Sub


============
END OF CODE
============

For anyone else interested in this example Excel file, don't hesitate to ask - just email me, and I will return the file. ...Dale Watson dwatson@bsi.gov.mb.ca