Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro column selection
Hello,
I have a that is written to format a form. The problem I am having is when a column is selected from within a macro to change its width, several columns get selected and their width changed. An example extracted from the macro below is : Columns("A:A").Select Selection.ColumnWidth = 6.33 Can anyone help with determining the solution!! Thanks!! Sub RecoveryLog() ' ' RecoveryLog Macro ' Macro recorded 10/5/2003 by XXXX' ' Keyboard Shortcut: Ctrl+r ' Workbooks.Add With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0) .RightMargin = Application.InchesToPoints(0) .TopMargin = Application.InchesToPoints(0) .BottomMargin = Application.InchesToPoints(0) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 90 .PrintErrors = xlPrintErrorsDisplayed End With Sheets("Sheet2").Select Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Sheets("Sheet3").Select ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True Range("A1:K1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "RECOVERY LOG" Range("L1:N1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Range("L1:N1").Select ActiveCell.FormulaR1C1 = "JUNE, 2003" Range("A2:A3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Range("A2:A3").Select ActiveCell.FormulaR1C1 = "DATE REC'D" Columns("A:A").Select Selection.ColumnWidth = 6.33 Range("A2:A3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("B2:B3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CHECK NO" Columns("B:B").Select Selection.ColumnWidth = 9.67 Range("B2:B3").Select With Selection\0 .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("C2:C3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "MARKER / PAYEE" Columns("C:C").Select Selection.ColumnWidth = 28.78 Range("C2:C3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("D2:D3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "AMOUNT" Columns("D:D").Select Selection.ColumnWidth = 7.56 Range("D2:D3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("E2:E3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CLAIM NO" Range("A1:A1").Select Columns("E:E").Select Selection.ColumnWidth = 8.22 Range("E2:E3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("F2:F3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "DATE ILLUMIN" Columns("F:F").Select Selection.ColumnWidth = 6.33 Range("F2:F3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("G2:G3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "DATE DEPOSITED" Columns("G:G").Select Selection.ColumnWidth = 8.22 Range("G2:G3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("H2:H3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CLIENT" Columns("H:H").Select Selection.ColumnWidth = 5.56 Range("H2:H3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("I2:I3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CLAIMANT NAME" Columns("I:I").Select Selection.ColumnWidth = 11.22 Range("I2:I3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("J2:J3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "INSURED NAME" Columns("J:J").Select Selection.ColumnWidth = 13.33 Range("J2:J3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("K2:K3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "REMARKS REFUND" Columns("K:K").Select Selection.ColumnWidth = 7.33 Range("K2:K3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("L2:L3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "REMARKS SUBRO" Columns("L:L").Select Selection.ColumnWidth = 7.56 Range("L2:L3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("M2:M3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "REMARKS SIF" Columns("M:M").Select Selection.ColumnWidth = 7.56 Range("M2:M3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("N2:N3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "PAYEE" Columns("N:N").Select Selection.ColumnWidth = 5.11 Range("N2:N3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Rows("1:3").Select Selection.Font.Bold = True Rows("4:4").Select ActiveWindow.FreezePanes = True Range("A1:K1").Select Columns("A:A").Select Range("A2").Activate Selection.NumberFormat = "m/d/yy;@" Columns("G:G").Select Range("G2").Activate Selection.NumberFormat = "m/d/yy;@" Columns("F:F").Select Range("F2").Activate Selection.NumberFormat = "m/d/yy;@" Columns("D:D").Select Range("D2").Activate Selection.NumberFormat = "#,##0.00" ActiveWindow.SmallScroll ToRight:=1 Columns("K:K").Select Range("K2").Activate Selection.NumberFormat = "#,##0.00" Rows("3:3").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Range("B4").Select Range("A40:C40").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "TOTAL FOR THE MONTH" Range("D40").Select Selection.FormulaR1C1 = "" ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("K40").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("L40").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("M40").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("M46").Select Range("E40:J40").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge With Selection.Interior .ColorIndex = 15 .Pattern = xlGray50 .PatternColorIndex = xlAutomatic End With ActiveWindow.SmallScroll ToRight:=1 Range("N40").Select With Selection.Interior .ColorIndex = 48 .Pattern = xlGray50 .PatternColorIndex = xlAutomatic End With Rows("1:1").Select With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Rows("2:3").Select Range("O2").Activate With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("A1:K1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro column selection
Hi Vispy
If this happen then you have merge a cell in the A column -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Vispy" wrote in message ... Hello, I have a that is written to format a form. The problem I am having is when a column is selected from within a macro to change its width, several columns get selected and their width changed. An example extracted from the macro below is : Columns("A:A").Select Selection.ColumnWidth = 6.33 Can anyone help with determining the solution!! Thanks!! Sub RecoveryLog() ' ' RecoveryLog Macro ' Macro recorded 10/5/2003 by XXXX' ' Keyboard Shortcut: Ctrl+r ' Workbooks.Add With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0) .RightMargin = Application.InchesToPoints(0) .TopMargin = Application.InchesToPoints(0) .BottomMargin = Application.InchesToPoints(0) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 90 .PrintErrors = xlPrintErrorsDisplayed End With Sheets("Sheet2").Select Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Sheets("Sheet3").Select ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True Range("A1:K1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "RECOVERY LOG" Range("L1:N1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Range("L1:N1").Select ActiveCell.FormulaR1C1 = "JUNE, 2003" Range("A2:A3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Range("A2:A3").Select ActiveCell.FormulaR1C1 = "DATE REC'D" Columns("A:A").Select Selection.ColumnWidth = 6.33 Range("A2:A3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("B2:B3").Select\0 With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CHECK NO" Columns("B:B").Select Selection.ColumnWidth = 9.67 Range("B2:B3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("C2:C3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "MARKER / PAYEE" Columns("C:C").Select Selection.ColumnWidth = 28.78 Range("C2:C3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("D2:D3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "AMOUNT" Columns("D:D").Select Selection.ColumnWidth = 7.56 Range("D2:D3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("E2:E3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CLAIM NO" Range("A1:A1").Select Columns("E:E").Select Selection.ColumnWidth = 8.22 Range("E2:E3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("F2:F3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "DATE ILLUMIN" Columns("F:F").Select Selection.ColumnWidth = 6.33 Range("F2:F3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("G2:G3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "DATE DEPOSITED" Columns("G:G").Select Selection.ColumnWidth = 8.22 Range("G2:G3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("H2:H3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CLIENT" Columns("H:H").Select Selection.ColumnWidth = 5.56 Range("H2:H3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("I2:I3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CLAIMANT NAME" Columns("I:I").Select Selection.ColumnWidth = 11.22 Range("I2:I3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("J2:J3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "INSURED NAME" Columns("J:J").Select Selection.ColumnWidth = 13.33 Range("J2:J3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("K2:K3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "REMARKS REFUND" Columns("K:K").Select Selection.ColumnWidth = 7.33 Range("K2:K3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("L2:L3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "REMARKS SUBRO" Columns("L:L").Select Selection.ColumnWidth = 7.56 Range("L2:L3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("M2:M3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "REMARKS SIF" Columns("M:M").Select Selection.ColumnWidth = 7.56 Range("M2:M3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("N2:N3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "PAYEE" Columns("N:N").Select Selection.ColumnWidth = 5.11 Range("N2:N3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Rows("1:3").Select Selection.Font.Bold = True Rows("4:4").Select ActiveWindow.FreezePanes = True Range("A1:K1").Select Columns("A:A").Select Range("A2").Activate Selection.NumberFormat = "m/d/yy;@" Columns("G:G").Select Range("G2").Activate Selection.NumberFormat = "m/d/yy;@" Columns("F:F").Select Range("F2").Activate Selection.NumberFormat = "m/d/yy;@" Columns("D:D").Select Range("D2").Activate Selection.NumberFormat = "#,##0.00" ActiveWindow.SmallScroll ToRight:=1 Columns("K:K").Select Range("K2").Activate Selection.NumberFormat = "#,##0.00" Rows("3:3").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Range("B4").Select Range("A40:C40").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "TOTAL FOR THE MONTH" Range("D40").Select Selection.FormulaR1C1 = "" ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("K40").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("L40").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("M40").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("M46").Select Range("E40:J40").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge With Selection.Interior .ColorIndex = 15 .Pattern = xlGray50 .PatternColorIndex = xlAutomatic End With ActiveWindow.SmallScroll ToRight:=1 Range("N40").Select With Selection.Interior .ColorIndex = 48 .Pattern = xlGray50 .PatternColorIndex = xlAutomatic End With Rows("1:1").Select With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Rows("2:3").Select Range("O2").Activate With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("A1:K1").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro column selection
Your code should change only the column width of Column A
unless there are merged cells within Column A. I therefore assume that this is the case. The problem will be corrected if you avoid selecting the column. The following code is suggested: Columns("A:A").ColumnWidth = 6.33 Note that selecting a range is almost always unnecessary and is best avoided. Regards, Greg -----Original Message----- Hello, I have a that is written to format a form. The problem I am having is when a column is selected from within a macro to change its width, several columns get selected and their width changed. An example extracted from the macro below is : Columns("A:A").Select Selection.ColumnWidth = 6.33 Can anyone help with determining the solution!! Thanks!! Sub RecoveryLog() ' ' RecoveryLog Macro ' Macro recorded 10/5/2003 by XXXX' ' Keyboard Shortcut: Ctrl+r ' Workbooks.Add With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0) .RightMargin = Application.InchesToPoints(0) .TopMargin = Application.InchesToPoints(0) .BottomMargin = Application.InchesToPoints(0) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 90 .PrintErrors = xlPrintErrorsDisplayed End With Sheets("Sheet2").Select Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Sheets("Sheet3").Select ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True Range("A1:K1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "RECOVERY LOG" Range("L1:N1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Range("L1:N1").Select ActiveCell.FormulaR1C1 = "JUNE, 2003" Range("A2:A3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Range("A2:A3").Select ActiveCell.FormulaR1C1 = "DATE REC'D" Columns("A:A").Select Selection.ColumnWidth = 6.33 Range("A2:A3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True\0 End With Range("B2:B3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CHECK NO" Columns("B:B").Select Selection.ColumnWidth = 9.67 Range("B2:B3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("C2:C3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "MARKER / PAYEE" Columns("C:C").Select Selection.ColumnWidth = 28.78 Range("C2:C3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("D2:D3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "AMOUNT" Columns("D:D").Select Selection.ColumnWidth = 7.56 Range("D2:D3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("E2:E3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CLAIM NO" Range("A1:A1").Select Columns("E:E").Select Selection.ColumnWidth = 8.22 Range("E2:E3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("F2:F3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "DATE ILLUMIN" Columns("F:F").Select Selection.ColumnWidth = 6.33 Range("F2:F3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("G2:G3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "DATE DEPOSITED" Columns("G:G").Select Selection.ColumnWidth = 8.22 Range("G2:G3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("H2:H3").Select With Selection .HorizontalAlignment = xlCenter VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CLIENT" Columns("H:H").Select Selection.ColumnWidth = 5.56 Range("H2:H3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("I2:I3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CLAIMANT NAME" Columns("I:I").Select Selection.ColumnWidth = 11.22 Range("I2:I3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("J2:J3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "INSURED NAME" Columns("J:J").Select Selection.ColumnWidth = 13.33 Range("J2:J3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("K2:K3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "REMARKS REFUND" Columns("K:K").Select Selection.ColumnWidth = 7.33 Range("K2:K3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("L2:L3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "REMARKS SUBRO" Columns("L:L").Select Selection.ColumnWidth = 7.56 Range("L2:L3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("M2:M3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "REMARKS SIF" Columns("M:M").Select Selection.ColumnWidth = 7.56 Range("M2:M3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("N2:N3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "PAYEE" Columns("N:N").Select Selection.ColumnWidth = 5.11 Range("N2:N3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Rows("1:3").Select Selection.Font.Bold = True Rows("4:4").Select ActiveWindow.FreezePanes = True Range("A1:K1").Select Columns("A:A").Select Range("A2").Activate Selection.NumberFormat = "m/d/yy;@" Columns("G:G").Select Range("G2").Activate Selection.NumberFormat = "m/d/yy;@" Columns("F:F").Select Range("F2").Activate Selection.NumberFormat = "m/d/yy;@" Columns("D:D").Select Range("D2").Activate Selection.NumberFormat = "#,##0.00" ActiveWindow.SmallScroll ToRight:=1 Columns("K:K").Select Range("K2").Activate Selection.NumberFormat = "#,##0.00" Rows("3:3").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Range("B4").Select Range("A40:C40").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "TOTAL FOR THE MONTH" Range("D40").Select Selection.FormulaR1C1 = "" ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("K40").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("L40").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("M40").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("M46").Select Range("E40:J40").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge With Selection.Interior .ColorIndex = 15 .Pattern = xlGray50 .PatternColorIndex = xlAutomatic End With ActiveWindow.SmallScroll ToRight:=1 Range("N40").Select With Selection.Interior .ColorIndex = 48 .Pattern = xlGray50 .PatternColorIndex = xlAutomatic End With Rows("1:1").Select With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Rows("2:3").Select Range("O2").Activate With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("A1:K1").Select End Sub . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro column selection
Hi Vispy
I forgot to tell you how to fix it but Greg did already I see.. Try to avoid merge cells also it will only give you trouble -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi Vispy If this happen then you have merge a cell in the A column -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Vispy" wrote in message ... Hello, I have a that is written to format a form. The problem I am having is when a column is selected from within a macro to change its width, several columns get selected and their width changed. An example extracted from the macro below is : Columns("A:A").Select Selection.ColumnWidth = 6.33 Can anyone help with determining the solution!! Thanks!! Sub RecoveryLog() ' ' RecoveryLog Macro ' Macro recorded 10/5/2003 by XXXX' ' Keyboard Shortcut: Ctrl+r ' Workbooks.Add With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0) .RightMargin = Application.InchesToPoints(0) .TopMargin = Application.InchesToPoints(0) .BottomMargin = Application.InchesToPoints(0) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 90 .PrintErrors = xlPrintErrorsDisplayed End With Sheets("Sheet2").Select Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Sheets("Sheet3").Select ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True Range("A1:K1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "RECOVERY LOG" Range("L1:N1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Range("L1:N1").Select ActiveCell.FormulaR1C1 = "JUNE, 2003" Range("A2:A3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With\0 Selection.Merge Range("A2:A3").Select ActiveCell.FormulaR1C1 = "DATE REC'D" Columns("A:A").Select Selection.ColumnWidth = 6.33 Range("A2:A3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("B2:B3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CHECK NO" Columns("B:B").Select Selection.ColumnWidth = 9.67 Range("B2:B3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("C2:C3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "MARKER / PAYEE" Columns("C:C").Select Selection.ColumnWidth = 28.78 Range("C2:C3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("D2:D3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "AMOUNT" Columns("D:D").Select Selection.ColumnWidth = 7.56 Range("D2:D3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("E2:E3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CLAIM NO" Range("A1:A1").Select Columns("E:E").Select Selection.ColumnWidth = 8.22 Range("E2:E3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("F2:F3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "DATE ILLUMIN" Columns("F:F").Select Selection.ColumnWidth = 6.33 Range("F2:F3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("G2:G3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "DATE DEPOSITED" Columns("G:G").Select Selection.ColumnWidth = 8.22 Range("G2:G3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("H2:H3").Select With Slection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CLIENT" Columns("H:H").Select Selection.ColumnWidth = 5.56 Range("H2:H3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("I2:I3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CLAIMANT NAME" Columns("I:I").Select Selection.ColumnWidth = 11.22 Range("I2:I3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("J2:J3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "INSURED NAME" Columns("J:J").Select Selection.ColumnWidth = 13.33 Range("J2:J3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("K2:K3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "REMARKS REFUND" Columns("K:K").Select Selection.ColumnWidth = 7.33 Range("K2:K3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("L2:L3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "REMARKS SUBRO" Columns("L:L").Select Selection.ColumnWidth = 7.56 Range("L2:L3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("M2:M3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "REMARKS SIF" Columns("M:M").Select Selection.ColumnWidth = 7.56 Range("M2:M3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("N2:N3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "PAYEE" Columns("N:N").Select Selection.ColumnWidth = 5.11 Range("N2:N3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Rows("1:3").Select Selection.Font.Bold = True Rows("4:4").Select ActiveWindow.FreezePanes = True Range("A1:K1").Select Columns("A:A").Select Range("A2").Activate Selection.NumberFormat = "m/d/yy;@" Columns("G:G").Select Range("G2").Activate Selection.NumberFormat = "m/d/yy;@" Columns("F:F").Select Range("F2").Activate Selection.NumberFormat = "m/d/yy;@" Columns("D:D").Select Range("D2").Activate Selection.NumberFormat = "#,##0.00" ActiveWindow.SmallScroll ToRight:=1 Columns("K:K").Select Range("K2").Activate Selection.NumberFormat = "#,##0.00" Rows("3:3").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Range("B4").Select Range("A40:C40").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "TOTAL FOR THE MONTH" Range("D40").Select Selection.FormulaR1C1 = "" ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("K40").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("L40").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("M40").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("M46").Select Range("E40:J40").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge With Selection.Interior .ColorIndex = 15 .Pattern = xlGray50 .PatternColorIndex = xlAutomatic End With ActiveWindow.SmallScroll ToRight:=1 Range("N40").Select With Selection.Interior .ColorIndex = 48 .Pattern = xlGray50 .PatternColorIndex = xlAutomatic End With Rows("1:1").Select With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Rows("2:3").Select Range("O2").Activate With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("A1:K1").Select End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro column selection
Thanks, Greg and Ron!!! I do have merged cells in column A. The fix
corrected the issue. Thanks a bunch!!!!! "Ron de Bruin" wrote in message ... Hi Vispy If this happen then you have merge a cell in the A column -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Vispy" wrote in message ... Hello, I have a that is written to format a form. The problem I am having is when a column is selected from within a macro to change its width, several columns get selected and their width changed. An example extracted from the macro below is : Columns("A:A").Select Selection.ColumnWidth = 6.33 Can anyone help with determining the solution!! Thanks!! Sub RecoveryLog() ' ' RecoveryLog Macro ' Macro recorded 10/5/2003 by XXXX' ' Keyboard Shortcut: Ctrl+r ' Workbooks.Add With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0) .RightMargin = Application.InchesToPoints(0) .TopMargin = Application.InchesToPoints(0) .BottomMargin = Application.InchesToPoints(0) .HeaderMargin = Application.InchesToPoints(0) .FooterMargin = Application.InchesToPoints(0) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 90 .PrintErrors = xlPrintErrorsDisplayed End With Sheets("Sheet2").Select Application.DisplayAlerts = False ActiveWindow.SelectedSheets.Delete Sheets("Sheet3").Select ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True Range("A1:K1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "RECOVERY LOG" Range("L1:N1").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Range("L1:N1").Select ActiveCell.FormulaR1C1 = "JUNE, 2003" Range("A2:A3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge Range("A2:A3").Select ActiveCell.FormulaR1C1 = "DATE REC'D"\0 Columns("A:A").Select Selection.ColumnWidth = 6.33 Range("A2:A3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("B2:B3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CHECK NO" Columns("B:B").Select Selection.ColumnWidth = 9.67 Range("B2:B3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("C2:C3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "MARKER / PAYEE" Columns("C:C").Select Selection.ColumnWidth = 28.78 Range("C2:C3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("D2:D3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "AMOUNT" Columns("D:D").Select Selection.ColumnWidth = 7.56 Range("D2:D3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("E2:E3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CLAIM NO" Range("A1:A1").Select Columns("E:E").Select Selection.ColumnWidth = 8.22 Range("E2:E3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("F2:F3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "DATE ILLUMIN" Columns("F:F").Select Selection.ColumnWidth = 6.33 Range("F2:F3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("G2:G3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "DATE DEPOSITED" Columns("G:G").Select Selection.ColumnWidth = 8.22 Range("G2:G3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("H2:H3").Select With Selection .HorizontalAlignment = xlCentr .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CLIENT" Columns("H:H").Select Selection.ColumnWidth = 5.56 Range("H2:H3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("I2:I3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "CLAIMANT NAME" Columns("I:I").Select Selection.ColumnWidth = 11.22 Range("I2:I3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("J2:J3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "INSURED NAME" Columns("J:J").Select Selection.ColumnWidth = 13.33 Range("J2:J3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("K2:K3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "REMARKS REFUND" Columns("K:K").Select Selection.ColumnWidth = 7.33 Range("K2:K3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("L2:L3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "REMARKS SUBRO" Columns("L:L").Select Selection.ColumnWidth = 7.56 Range("L2:L3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("M2:M3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "REMARKS SIF" Columns("M:M").Select Selection.ColumnWidth = 7.56 Range("M2:M3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Range("N2:N3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "PAYEE" Columns("N:N").Select Selection.ColumnWidth = 5.11 Range("N2:N3").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With Rows("1:3").Select Selection.Font.Bold = True Rows("4:4").Select ActiveWindow.FreezePanes = True Range("A1:K1").Select Columns("A:A").Select Range("A2").Activate Selection.NumberFormat = "m/d/yy;@" Columns("G:G").Select Range("G2").Activate Selection.NumberFormat = "m/d/yy;@" Columns("F:F").Select Range("F2").Activate Selection.NumberFormat = "m/d/yy;@" Columns("D:D").Select Range("D2").Activate Selection.NumberFormat = "#,##0.00" ActiveWindow.SmallScroll ToRight:=1 Columns("K:K").Select Range("K2").Activate Selection.NumberFormat = "#,##0.00" Rows("3:3").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = xlAutomatic End With Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Range("B4").Select Range("A40:C40").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge ActiveCell.FormulaR1C1 = "TOTAL FOR THE MONTH" Range("D40").Select Selection.FormulaR1C1 = "" ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("K40").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("L40").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("M40").Select ActiveCell.FormulaR1C1 = "=SUM(R[-1]C:R[-36]C)" Range("M46").Select Range("E40:J40").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection.Merge With Selection.Interior .ColorIndex = 15 .Pattern = xlGray50 .PatternColorIndex = xlAutomatic End With ActiveWindow.SmallScroll ToRight:=1 Range("N40").Select With Selection.Interior .ColorIndex = 48 .Pattern = xlGray50 .PatternColorIndex = xlAutomatic End With Rows("1:1").Select With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Rows("2:3").Select Range("O2").Activate With Selection.Font .Name = "Arial" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("A1:K1").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro help - add column selection | Excel Worksheet Functions | |||
How can run a macro ( call a macro) on selection of any filtercriteria? | Excel Worksheet Functions | |||
SELECTION macro | Excel Discussion (Misc queries) | |||
Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
Macro for row selection | Excel Discussion (Misc queries) |