Well, for instance,

Public Function FormatHeaders(ByRef ws As Worksheet)
'format row and columns
ws.Rows("1:1").RowHeight = 45
ws.Rows("2:2").RowHeight = 15.75
ws.Rows("3:3").RowHeight = 21.75
ws.Rows("4:4").RowHeight = 13
ws.Rows("5:21").RowHeight = 33
ws.Columns("A:A").ColumnWidth = 6.57
ws.Columns("E:F").ColumnWidth = 8.43
ws.Columns("G:G").ColumnWidth = 15
ws.Columns("H:H").ColumnWidth = 2.96
ws.Columns("L:M").ColumnWidth = 6.14

etc., an when calling it use

Call FormatHeaders(ws:=Worksheets("the name of the worksheet to be
formatted")

and so on with the other functions.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Mekinnik" wrote in message
...
Sorry Bob,
That is just a tad bit beyond me, I am learning as I go and I do not quite
understand how to do what you suggest and am not sure then end result
would
be what I have visioned.

"Bob Phillips" wrote:

Maybe you should pass the worksheet to be worked upon as parameters to
the
called functions, and qualify the ranges with the worksheet object.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Mekinnik" wrote in message
...
Thank you for pointing that out. The problem I am having is with the
formatHeaders sub, and it works for the first time, however it will not
format after the page break because of the fact that I have it
formatting
particular cells and rows and I need the sub to determine how many
rows
of
data and then format however many pages it needs. I could if you would
like
send you what I have and maybe it would help to understand what it that
I
am
trying to accomplish.

"FSt1" wrote:

hi
change...
public function formatheaders()
to..
sub formatheaders()
and change end function at the bottom to end sub.
it should work now.

subs are code that do things. functions create custom formulas and
such
and
are not ment to perform direct actions like subs.

Regards
FSt1

"Mekinnik" wrote:

Here are the function:

Public Function FormatHeaders()
'format row and columns
Rows("1:1").RowHeight = 45
Rows("2:2").RowHeight = 15.75
Rows("3:3").RowHeight = 21.75
Rows("4:4").RowHeight = 13
Rows("5:21").RowHeight = 33
Columns("A:A").ColumnWidth = 6.57
Columns("E:F").ColumnWidth = 8.43
Columns("G:G").ColumnWidth = 15
Columns("H:H").ColumnWidth = 2.96
Columns("L:M").ColumnWidth = 6.14
Columns("N:N").ColumnWidth = 6.29
Range("C:C,O:O,P:P").ColumnWidth = 6.86
Columns("Q:Q").ColumnWidth = 8.71
Range("B:B,D:D,I:K").ColumnWidth = 6

'formats row 1 for heading
With Range("A1:Q1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 36
.Font.Bold = True
Range("A1").FormulaR1C1 = "Hazardous Material Inventory"
End With
'formats row 2
With Range("A2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Font.Name = "Arial"
.Font.Size = 12
.Font.Bold = True
Range("A2").FormulaR1C1 = "Unit:"
End With
With Range("B2:E2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Merge
.Font.Name = "Arial"
.Font.Size = 12
End With
With Range("A2:E2")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
End With
With Range("F2:G2")
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 12
.Font.Bold = True
Range("F2").FormulaR1C1 = "Department/Division:"
End With
With Range("H2:L2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
With Range("M2")
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Font.Name = "Arial"
.Font.Size = 12
.Font.Bold = True
Range("M2").FormulaR1C1 = "Date:"
End With
With Range("N2:Q2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
'format coulmn 3
With Range("A3:A4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 9
.Font.Bold = True
Range("A3").FormulaR1C1 = "MSDS#"
End With
With Range("B3:E4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 9
.Font.Bold = True
Range("B3").FormulaR1C1 = "Product Name"
End With
With Range("F3:G4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.WrapText = True
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("F3").FormulaR1C1 = "Manufacturers Name Phone
Number"
End With
With Range("H3:H4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.WrapText = True
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("H3").FormulaR1C1 = "A / I"
End With
With Range("I3:I4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.WrapText = True
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("I3").FormulaR1C1 = "EHS (302) YES/NO"
End With
With Range("J3:J4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.WrapText = True
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("J3").FormulaR1C1 = "Toxic (313) YES/NO"
End With
With Range("K3:N3")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Merge
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 9
.Font.Bold = True
Range("K3").FormulaR1C1 = "NFPA/HMIS Rating"
End With
With Range("K4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("K4").FormulaR1C1 = "Fire"
End With
With Range("L4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("L4").FormulaR1C1 = "Health"
End With
With Range("M4")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Font.Name = "Arial"
.Font.Size = 8
.Font.Bold = True
Range("M4").FormulaR1C1 = "React"
End With
With Range("N4")
.HorizontalAlignment = xlCenter