Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Table format | Excel Discussion (Misc queries) | |||
Pivot Table format ! | Excel Discussion (Misc queries) | |||
pivot table format | Excel Discussion (Misc queries) | |||
table format | Excel Discussion (Misc queries) | |||
Data in Cross-Tab format: needs to be written in Table Format | Excel Programming |