Better way to do this?

[wmtsub]

I am trying to ensure that a worksheet always has the formatted view I desire. So I did the "Record Macro" and I run it when I need to correct the view. But it is very slow.

is there a more efficient [quicker] way todo this?


' Format
Cells.Select
Selection.Hyperlinks.Delete
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Font.Underline = xlUnderlineStyleSingle
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
With Selection.Font
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
With Selection.Font
.Name = "Calibri"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Selection.Font.Bold = True
Selection.Font.Bold = False
Cells.Select
Cells.EntireColumn.AutoFit

 

[Norie]

Do you definitely want to format all the cells on the worksheet?

 

[wmtsub]

all the active cells.

 

[Fluff]

You could try this

   With ActiveSheet.UsedRange
      .Hyperlinks.Delete
      .Borders.LineStyle = xlNone
      .Borders(xlDiagonalUp).LineStyle = xlNone
      With .Interior
         .Pattern = xlNone
         .TintAndShade = 0
         .PatternTintAndShade = 0
      End With
      With .Font
         .name = "Calibri"
         .ColorIndex = xlAutomatic
         .TintAndShade = 0
         .Underline = xlUnderlineStyleNone
         .Bold = False
         .size = 12
         .Strikethrough = False
         .Superscript = False
         .Subscript = False
         .OutlineFont = False
         .Shadow = False
      End With
      .EntireColumn.AutoFit
   End With

 

[shg]

Cells.Style = "Normal"

Adjust style Normal to preference.