On 12 Sep., 19:01, wrote:
On 12 Sep., 17:38, Naz wrote:



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

Forgot to add the border...

Insert this line after the last end with:

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