format table

[Naz]

Hi

I have a standard table format that i use. I want to create a macro that
will apply this format when i select a range and run it in this sort of
sequence

Sub FormatTable()

First row within range
Format1

Rows 2 to 1 before last
Format 2

Last row
Format 3

Whole table
Border 1

End sub

I know how to address ranges, and how to code the formats i need. But dont't
know who to tell VBA which part of the range i want to apply formatting to
i.e. top row, middle section or bottom row.

All help is appreciated.

_______________________
Naz,
London

 

[matthias.karl]

Hi

I have a standard table format that i use. I want to create a macro that
will apply this format when i select a range and run it in this sort of
sequence

Sub FormatTable()

First row within range
Format1

Rows 2 to 1 before last
Format 2

Last row
Format 3

Whole table
Border 1

End sub

I know how to address ranges, and how to code the formats i need. But dont't
know who to tell VBA which part of the range i want to apply formatting to
i.e. top row, middle section or bottom row.

All help is appreciated.

_______________________
Naz,
London

Naz

This procedure will format the actual selection as you want.

Sub FormatSelection()

'Initialize variables
Dim MrngToformat As Range
Dim MintRows As Integer
Dim MintCols As Integer

Set MrngToformat = ActiveWindow.RangeSelection 'Use active
selection

'Count rows and columns
MintRows = MrngToformat.Rows.Count
MintCols = MrngToformat.Columns.Count

'Format top-row
With Range(MrngToformat(1, 1), MrngToformat(1, MintCols))
.Font.Bold = True
.Font.Name = "Times"
End With

'Format rows in between
With Range(MrngToformat(2, 1), MrngToformat(MintRows - 1,
MintCols))
.Font.Italic = True
.Font.Name = "Verdana"
End With

'Format bottom row
With Range(MrngToformat(MintRows, 1), MrngToformat(MintRows,
MintCols))
.Font.Underline = True
.Font.Name = "Tahoma"
End With

Set MrngToformat = Nothing 'Delete range

End Sub

 

[matthias.karl]

Naz

This procedure will format the actual selection as you want.

Sub FormatSelection()

   'Initialize variables
   Dim MrngToformat As Range
   Dim MintRows As Integer
   Dim MintCols As Integer

   Set MrngToformat = ActiveWindow.RangeSelection 'Use active
selection

   'Count rows and columns
   MintRows = MrngToformat.Rows.Count
   MintCols = MrngToformat.Columns.Count

   'Format top-row
   With Range(MrngToformat(1, 1), MrngToformat(1, MintCols))
      .Font.Bold = True
      .Font.Name = "Times"
   End With

   'Format rows in between
   With Range(MrngToformat(2, 1), MrngToformat(MintRows - 1,
MintCols))
      .Font.Italic = True
      .Font.Name = "Verdana"
   End With

   'Format bottom row
   With Range(MrngToformat(MintRows, 1), MrngToformat(MintRows,
MintCols))
      .Font.Underline = True
      .Font.Name = "Tahoma"
   End With

   Set MrngToformat = Nothing 'Delete range

End Sub

Forgot to add the border...

Insert this line after the last end with:

MrngToformat.BorderAround LineStyle:=xlContinuous, Weight:=xlMedium,
ColorIndex:=1

 

[Naz]

Perfecto!
Many thanks