format table
 

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

format table
 

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

format table
 

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

format table
 

Perfecto!
Many thanks
--

_______________________
Naz,
London


" wrote:

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