My code to format my sheets is below, but it runs very very slowly....any way I can make this more streamlined?
Sub FormattingGlobal()
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet.Range("A1:K300")
.FormatConditions.Delete
End With
Range("A17:K190").Select
With Selection.Font
.Name = "Arial"
.Size = 9
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A17:A190").Select
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A193:E250").Select
With Selection.Font
.Name = "Arial"
.Size = 9
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A17:A190").Select
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Underline = xlUnderlineStyleNone
ActiveWindow.Zoom = 90
ActiveWindow.SmallScroll Down:=-250
For Each x In Range("C17:C190,C193:C250,K17:K190")
x.Value = Application.Proper(x.Value)
Next
For Each x In Range("A17:A190,A193:A250,D17:D190")
x.Value = UCase(x.Value)
Next
Range("A18:F190,I18:K190").Select
Selection.NumberFormat = "General"
Range("B17:B190").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="EU Corporate", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10092288
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("O17:O190").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(F17=""Renewal"",O17=""N"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("J17:J190").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Amber", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("J17:J190").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Green", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13434777
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("J17:J190").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Red", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 8420607
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("B17:B190").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Company", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 14734864
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("B17:B190").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Syndicate", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("B17:B190").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Bermuda", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("C18:C190").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=J18=""Red"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 8420607
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("C18:C190").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=J18=""Amber"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("C18:C190").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=J18=""Green"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13434777
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("B193:B300").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="EU Corporate", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 10092288
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("B193:B300").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Syndicate", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("B193:B300").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Company", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 14734864
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Range("M16").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
Columns.AutoFit
Range("A15").Select
End Sub
Bookmarks