Inconsistent Macro Behavior

I have a client who created a macro that applies different formatting to
columns in her spreadsheet. When she runs it on a new, blank spreadsheet, it
works fine. When she uses it on a spreadsheet created from one of our
proprietary applications (spreadsheet starts out html, not xls), instead of
highlighting just the one column, it highlights the entire spreadsheet and
applies the formatting to all the columns.

Here are some of the things I've tried:
1. Happens in both Excel XP and 2003
2. Saved file as .xls first, then ran macro (still happens)
3. Tried on different computers (still happens)
4. Copied content and pasted only values in new worksheet (macro works
fine - but there are other issues with doing that)

Any ideas on why the macro highlights all columns?

Hard to guess without seeing the code.

Jerry

Sarah K wrote:

> I have a client who created a macro that applies different formatting to
> columns in her spreadsheet. When she runs it on a new, blank spreadsheet, it
> works fine. When she uses it on a spreadsheet created from one of our
> proprietary applications (spreadsheet starts out html, not xls), instead of
> highlighting just the one column, it highlights the entire spreadsheet and
> applies the formatting to all the columns.
>
> Here are some of the things I've tried:
> 1. Happens in both Excel XP and 2003
> 2. Saved file as .xls first, then ran macro (still happens)
> 3. Tried on different computers (still happens)
> 4. Copied content and pasted only values in new worksheet (macro works
> fine - but there are other issues with doing that)
>
> Any ideas on why the macro highlights all columns?

Here's the code:
Sub Construction()
'
' Construction Macro
' Macro recorded 10/25/2005 by jmcgoogan
'

'
Cells.Select
With Selection.Font
.Name = "Arial Narrow"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
With Selection.Font
.Name = "Arial Narrow"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Range("E5").Select
ActiveWindow.WindowState = xlMaximized
Range("A6:M93").Select
Range("A93").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("L100").Select
Range("A1").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&""Arial Narrow,Regular""&8Advance Mechanical Systems"
.CenterFooter = ""
.RightFooter = "&""Arial Narrow,Regular""&8Page &P"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Columns("A:A").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.ColumnWidth = 6.43
Rows("9:9").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").ColumnWidth = 17
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.ColumnWidth = 5.43
Selection.ColumnWidth = 7
Columns("D:D").ColumnWidth = 8.71
Columns("E:E").ColumnWidth = 25
Columns("F:F").ColumnWidth = 9
Columns("F:F").ColumnWidth = 7.86
Columns("F:F").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Columns("G:G").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Columns("G:G").Select
Selection.ColumnWidth = 6.57
Columns("H:I").Select
Selection.NumberFormat = "$#,##0"
Selection.ColumnWidth = 6.43
ActiveWindow.SmallScroll ToRight:=6
Selection.ColumnWidth = 9.14
Columns("H:H").Select
Selection.ColumnWidth = 6.29
Columns("J:J").Select
Selection.ColumnWidth = 7.14
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Selection.ColumnWidth = 6.29
Columns("K:K").ColumnWidth = 8
Columns("K:K").Select
Selection.NumberFormat = "$#,##0"
Columns("L:L").ColumnWidth = 7
Columns("M:M").ColumnWidth = 7.14
Columns("A:A").ColumnWidth = 7
Columns("A:A").ColumnWidth = 8
End Sub


"Jerry W. Lewis" wrote:

> Hard to guess without seeing the code.
>
> Jerry
>
> Sarah K wrote:
>
> > I have a client who created a macro that applies different formatting to
> > columns in her spreadsheet. When she runs it on a new, blank spreadsheet, it
> > works fine. When she uses it on a spreadsheet created from one of our
> > proprietary applications (spreadsheet starts out html, not xls), instead of
> > highlighting just the one column, it highlights the entire spreadsheet and
> > applies the formatting to all the columns.
> >
> > Here are some of the things I've tried:
> > 1. Happens in both Excel XP and 2003
> > 2. Saved file as .xls first, then ran macro (still happens)
> > 3. Tried on different computers (still happens)
> > 4. Copied content and pasted only values in new worksheet (macro works
> > fine - but there are other issues with doing that)
> >
> > Any ideas on why the macro highlights all columns?
>
>

Just a guess...

Do you have any merged cells in that worksheet?

If I recall correctly, different versions of excel would handle merged cells
differently.

(This is subject to a failing memory...)

Say you have A6:M6 merged as a giant cell;
(Format|cells|alignment tab|check Merge cells)

I think if you selected (say) column C, then xl97 would select all of A:M.
Later versions of xl handled the merged cells better. (I use xl2003 and
selecting column C just selected column C.)



Sarah K wrote:
>
> Here's the code:
> Sub Construction()
> '
> ' Construction Macro
> ' Macro recorded 10/25/2005 by jmcgoogan
> '
>
> '
> Cells.Select
> With Selection.Font
> .Name = "Arial Narrow"
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> End With
> With Selection.Font
> .Name = "Arial Narrow"
> .Size = 8
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> End With
> Range("E5").Select
> ActiveWindow.WindowState = xlMaximized
> Range("A6:M93").Select
> Range("A93").Activate
> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> With Selection.Borders(xlEdgeLeft)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeTop)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeBottom)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlEdgeRight)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlInsideVertical)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = xlAutomatic
> End With
> With Selection.Borders(xlInsideHorizontal)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = xlAutomatic
> End With
> Range("L100").Select
> Range("A1").Select
> With ActiveSheet.PageSetup
> .PrintTitleRows = ""
> .PrintTitleColumns = ""
> End With
> ActiveSheet.PageSetup.PrintArea = ""
> With ActiveSheet.PageSetup
> .LeftHeader = ""
> .CenterHeader = ""
> .RightHeader = ""
> .LeftFooter = "&""Arial Narrow,Regular""&8Advance Mechanical Systems"
> .CenterFooter = ""
> .RightFooter = "&""Arial Narrow,Regular""&8Page &P"
> .LeftMargin = Application.InchesToPoints(0.25)
> .RightMargin = Application.InchesToPoints(0.25)
> .TopMargin = Application.InchesToPoints(0.75)
> .BottomMargin = Application.InchesToPoints(0.75)
> .HeaderMargin = Application.InchesToPoints(0.5)
> .FooterMargin = Application.InchesToPoints(0.5)
> .PrintHeadings = False
> .PrintGridlines = False
> .PrintComments = xlPrintNoComments
> .PrintQuality = 600
> .CenterHorizontally = False
> .CenterVertically = False
> .Orientation = xlLandscape
> .Draft = False
> .PaperSize = xlPaperLetter
> .FirstPageNumber = xlAutomatic
> .Order = xlDownThenOver
> .BlackAndWhite = False
> .Zoom = 100
> .PrintErrors = xlPrintErrorsDisplayed
> End With
> Columns("A:A").Select
> With Selection
> .HorizontalAlignment = xlLeft
> .VerticalAlignment = xlBottom
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> End With
> Selection.ColumnWidth = 6.43
> Rows("9:9").Select
> With Selection
> .VerticalAlignment = xlBottom
> .WrapText = True
> .Orientation = 0
> .AddIndent = False
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> .MergeCells = False
> End With
> Columns("B:B").ColumnWidth = 17
> Columns("C:C").Select
> With Selection
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> End With
> Selection.ColumnWidth = 5.43
> Selection.ColumnWidth = 7
> Columns("D:D").ColumnWidth = 8.71
> Columns("E:E").ColumnWidth = 25
> Columns("F:F").ColumnWidth = 9
> Columns("F:F").ColumnWidth = 7.86
> Columns("F:F").Select
> With Selection
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> End With
> Columns("G:G").Select
> With Selection
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> End With
> Columns("G:G").Select
> Selection.ColumnWidth = 6.57
> Columns("H:I").Select
> Selection.NumberFormat = "$#,##0"
> Selection.ColumnWidth = 6.43
> ActiveWindow.SmallScroll ToRight:=6
> Selection.ColumnWidth = 9.14
> Columns("H:H").Select
> Selection.ColumnWidth = 6.29
> Columns("J:J").Select
> Selection.ColumnWidth = 7.14
> With Selection
> .HorizontalAlignment = xlCenter
> .VerticalAlignment = xlBottom
> .Orientation = 0
> .AddIndent = False
> .IndentLevel = 0
> .ShrinkToFit = False
> .ReadingOrder = xlContext
> End With
> Selection.ColumnWidth = 6.29
> Columns("K:K").ColumnWidth = 8
> Columns("K:K").Select
> Selection.NumberFormat = "$#,##0"
> Columns("L:L").ColumnWidth = 7
> Columns("M:M").ColumnWidth = 7.14
> Columns("A:A").ColumnWidth = 7
> Columns("A:A").ColumnWidth = 8
> End Sub
>
> "Jerry W. Lewis" wrote:
>
> > Hard to guess without seeing the code.
> >
> > Jerry
> >
> > Sarah K wrote:
> >
> > > I have a client who created a macro that applies different formatting to
> > > columns in her spreadsheet. When she runs it on a new, blank spreadsheet, it
> > > works fine. When she uses it on a spreadsheet created from one of our
> > > proprietary applications (spreadsheet starts out html, not xls), instead of
> > > highlighting just the one column, it highlights the entire spreadsheet and
> > > applies the formatting to all the columns.
> > >
> > > Here are some of the things I've tried:
> > > 1. Happens in both Excel XP and 2003
> > > 2. Saved file as .xls first, then ran macro (still happens)
> > > 3. Tried on different computers (still happens)
> > > 4. Copied content and pasted only values in new worksheet (macro works
> > > fine - but there are other issues with doing that)
> > >
> > > Any ideas on why the macro highlights all columns?
> >
> >

--

Dave Peterson

Dave,
Thank you. That's it. I didn't even THINK to look for merged cells.

Sarah

"Dave Peterson" wrote:

> Just a guess...
>
> Do you have any merged cells in that worksheet?
>
> If I recall correctly, different versions of excel would handle merged cells
> differently.
>
> (This is subject to a failing memory...)
>
> Say you have A6:M6 merged as a giant cell;
> (Format|cells|alignment tab|check Merge cells)
>
> I think if you selected (say) column C, then xl97 would select all of A:M.
> Later versions of xl handled the merged cells better. (I use xl2003 and
> selecting column C just selected column C.)
>
>
>
> Sarah K wrote:
> >
> > Here's the code:
> > Sub Construction()
> > '
> > ' Construction Macro
> > ' Macro recorded 10/25/2005 by jmcgoogan
> > '
> >
> > '
> > Cells.Select
> > With Selection.Font
> > .Name = "Arial Narrow"
> > .Strikethrough = False
> > .Superscript = False
> > .Subscript = False
> > .OutlineFont = False
> > .Shadow = False
> > .Underline = xlUnderlineStyleNone
> > End With
> > With Selection.Font
> > .Name = "Arial Narrow"
> > .Size = 8
> > .Strikethrough = False
> > .Superscript = False
> > .Subscript = False
> > .OutlineFont = False
> > .Shadow = False
> > .Underline = xlUnderlineStyleNone
> > End With
> > Range("E5").Select
> > ActiveWindow.WindowState = xlMaximized
> > Range("A6:M93").Select
> > Range("A93").Activate
> > Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> > Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> > With Selection.Borders(xlEdgeLeft)
> > .LineStyle = xlContinuous
> > .Weight = xlThin
> > .ColorIndex = xlAutomatic
> > End With
> > With Selection.Borders(xlEdgeTop)
> > .LineStyle = xlContinuous
> > .Weight = xlThin
> > .ColorIndex = xlAutomatic
> > End With
> > With Selection.Borders(xlEdgeBottom)
> > .LineStyle = xlContinuous
> > .Weight = xlThin
> > .ColorIndex = xlAutomatic
> > End With
> > With Selection.Borders(xlEdgeRight)
> > .LineStyle = xlContinuous
> > .Weight = xlThin
> > .ColorIndex = xlAutomatic
> > End With
> > With Selection.Borders(xlInsideVertical)
> > .LineStyle = xlContinuous
> > .Weight = xlThin
> > .ColorIndex = xlAutomatic
> > End With
> > With Selection.Borders(xlInsideHorizontal)
> > .LineStyle = xlContinuous
> > .Weight = xlThin
> > .ColorIndex = xlAutomatic
> > End With
> > Range("L100").Select
> > Range("A1").Select
> > With ActiveSheet.PageSetup
> > .PrintTitleRows = ""
> > .PrintTitleColumns = ""
> > End With
> > ActiveSheet.PageSetup.PrintArea = ""
> > With ActiveSheet.PageSetup
> > .LeftHeader = ""
> > .CenterHeader = ""
> > .RightHeader = ""
> > .LeftFooter = "&""Arial Narrow,Regular""&8Advance Mechanical Systems"
> > .CenterFooter = ""
> > .RightFooter = "&""Arial Narrow,Regular""&8Page &P"
> > .LeftMargin = Application.InchesToPoints(0.25)
> > .RightMargin = Application.InchesToPoints(0.25)
> > .TopMargin = Application.InchesToPoints(0.75)
> > .BottomMargin = Application.InchesToPoints(0.75)
> > .HeaderMargin = Application.InchesToPoints(0.5)
> > .FooterMargin = Application.InchesToPoints(0.5)
> > .PrintHeadings = False
> > .PrintGridlines = False
> > .PrintComments = xlPrintNoComments
> > .PrintQuality = 600
> > .CenterHorizontally = False
> > .CenterVertically = False
> > .Orientation = xlLandscape
> > .Draft = False
> > .PaperSize = xlPaperLetter
> > .FirstPageNumber = xlAutomatic
> > .Order = xlDownThenOver
> > .BlackAndWhite = False
> > .Zoom = 100
> > .PrintErrors = xlPrintErrorsDisplayed
> > End With
> > Columns("A:A").Select
> > With Selection
> > .HorizontalAlignment = xlLeft
> > .VerticalAlignment = xlBottom
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > End With
> > Selection.ColumnWidth = 6.43
> > Rows("9:9").Select
> > With Selection
> > .VerticalAlignment = xlBottom
> > .WrapText = True
> > .Orientation = 0
> > .AddIndent = False
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > .MergeCells = False
> > End With
> > Columns("B:B").ColumnWidth = 17
> > Columns("C:C").Select
> > With Selection
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlBottom
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > End With
> > Selection.ColumnWidth = 5.43
> > Selection.ColumnWidth = 7
> > Columns("D:D").ColumnWidth = 8.71
> > Columns("E:E").ColumnWidth = 25
> > Columns("F:F").ColumnWidth = 9
> > Columns("F:F").ColumnWidth = 7.86
> > Columns("F:F").Select
> > With Selection
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlBottom
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > End With
> > Columns("G:G").Select
> > With Selection
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlBottom
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > End With
> > Columns("G:G").Select
> > Selection.ColumnWidth = 6.57
> > Columns("H:I").Select
> > Selection.NumberFormat = "$#,##0"
> > Selection.ColumnWidth = 6.43
> > ActiveWindow.SmallScroll ToRight:=6
> > Selection.ColumnWidth = 9.14
> > Columns("H:H").Select
> > Selection.ColumnWidth = 6.29
> > Columns("J:J").Select
> > Selection.ColumnWidth = 7.14
> > With Selection
> > .HorizontalAlignment = xlCenter
> > .VerticalAlignment = xlBottom
> > .Orientation = 0
> > .AddIndent = False
> > .IndentLevel = 0
> > .ShrinkToFit = False
> > .ReadingOrder = xlContext
> > End With
> > Selection.ColumnWidth = 6.29
> > Columns("K:K").ColumnWidth = 8
> > Columns("K:K").Select
> > Selection.NumberFormat = "$#,##0"
> > Columns("L:L").ColumnWidth = 7
> > Columns("M:M").ColumnWidth = 7.14
> > Columns("A:A").ColumnWidth = 7
> > Columns("A:A").ColumnWidth = 8
> > End Sub
> >
> > "Jerry W. Lewis" wrote:
> >
> > > Hard to guess without seeing the code.
> > >
> > > Jerry
> > >
> > > Sarah K wrote:
> > >
> > > > I have a client who created a macro that applies different formatting to
> > > > columns in her spreadsheet. When she runs it on a new, blank spreadsheet, it
> > > > works fine. When she uses it on a spreadsheet created from one of our
> > > > proprietary applications (spreadsheet starts out html, not xls), instead of
> > > > highlighting just the one column, it highlights the entire spreadsheet and
> > > > applies the formatting to all the columns.
> > > >
> > > > Here are some of the things I've tried:
> > > > 1. Happens in both Excel XP and 2003
> > > > 2. Saved file as .xls first, then ran macro (still happens)
> > > > 3. Tried on different computers (still happens)
> > > > 4. Copied content and pasted only values in new worksheet (macro works
> > > > fine - but there are other issues with doing that)
> > > >
> > > > Any ideas on why the macro highlights all columns?
> > >
> > >
>
> --
>
> Dave Peterson
>