Formatting sheets with macro running very slowly

**nickmax1** · 12-21-2012, 09:02 AM

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

**Norie** · 12-21-2012, 09:27 AM

Getting rid of all the Select/Selection in the code might speed things up a bit.

However, I think the main bottleneck is the two loops.

Not sure what you can do about them.

**aelgadi** · 12-21-2012, 09:33 AM

Hi nickmax1,
Try to avoid interactive command like ".Select"
for example instead of

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

use

With Range("A17:K190").Font
        .Name = "Arial"
        .Size = 9
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With

Try this and if you still have problem let us know

**nickmax1** · 12-21-2012, 09:40 AM

invalid use of property when i change . select to .font

??

**OllieB** · 12-21-2012, 09:47 AM

Hi Nick,

I have made all the changes needed in the code I posted above which runs without any errors

**OllieB** · 12-21-2012, 09:35 AM

What I notice is that you first select a range and then apply the formatting to the range. This is often not really necessary as you can apply formatting also with physically selecting the cells. I have personally found the latter method to be quicker. I have re-written some of the code and on my PC it runs in less than a second

Sub FormattingGlobal()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    With ActiveSheet.Range("A1:K300")
        .FormatConditions.Delete
    End With
    
     With Range("A17:K190")
          .HorizontalAlignment = xlLeft
          .VerticalAlignment = xlCenter
          .WrapText = False
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = False

          With .Font
               .Name = "Arial"
               .Size = 9
               .Superscript = False
               .Subscript = False
               .OutlineFont = False
               .Shadow = False
               .Underline = xlUnderlineStyleNone
               .TintAndShade = 0
               .ThemeFont = xlThemeFontNone
          End With
     End With
     
     With Range("A17:A190")
          .HorizontalAlignment = xlGeneral
          .VerticalAlignment = xlCenter
          .WrapText = False
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = False
          With .Font
               .ColorIndex = xlAutomatic
               .TintAndShade = 0
               .Underline = xlUnderlineStyleNone
          End With
     End With
    
     With Range("A193:E250")
          .HorizontalAlignment = xlLeft
          .VerticalAlignment = xlCenter
          .WrapText = False
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = False

          With .Font
               .Name = "Arial"
               .Size = 9
               .Superscript = False
               .Subscript = False
               .OutlineFont = False
               .Shadow = False
               .Underline = xlUnderlineStyleNone
               .TintAndShade = 0
               .ThemeFont = xlThemeFontNone
          End With
     End With
     
    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").NumberFormat = "General"
     
    With Range("B17:B190")
          .FormatConditions.Add Type:=xlTextString, String:="EU Corporate", TextOperator:=xlContains
          .FormatConditions(.FormatConditions.Count).SetFirstPriority
          .FormatConditions(1).StopIfTrue = True
          With .FormatConditions(1).Interior
               .PatternColorIndex = xlAutomatic
               .Color = 10092288
               .TintAndShade = 0
          End With
     End With
    
     With Range("O17:O190")
          .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(F17=""Renewal"",O17=""N"")"
          .FormatConditions(.FormatConditions.Count).SetFirstPriority
          .FormatConditions(1).StopIfTrue = True
          With .FormatConditions(1).Interior
               .PatternColorIndex = xlAutomatic
               .Color = 255
               .TintAndShade = 0
          End With
     End With

    With Range("J17:J190")
          .FormatConditions.Add Type:=xlTextString, String:="Amber", TextOperator:=xlContains
          .FormatConditions(.FormatConditions.Count).SetFirstPriority
          .FormatConditions(1).StopIfTrue = True
          With .FormatConditions(1).Interior
               .PatternColorIndex = xlAutomatic
               .Color = 49407
               .TintAndShade = 0
          End With
          
          .FormatConditions.Add Type:=xlTextString, String:="Green", TextOperator:=xlContains
          .FormatConditions(.FormatConditions.Count).SetFirstPriority
          .FormatConditions(1).StopIfTrue = True
          With .FormatConditions(1).Interior
               .PatternColorIndex = xlAutomatic
               .Color = 13434777
               .TintAndShade = 0
          End With
          
          .FormatConditions.Add Type:=xlTextString, String:="Red", TextOperator:=xlContains
          .FormatConditions(.FormatConditions.Count).SetFirstPriority
          .FormatConditions(1).StopIfTrue = True
          With .FormatConditions(1).Interior
               .PatternColorIndex = xlAutomatic
               .Color = 8420607
               .TintAndShade = 0
          End With
     End With
     
     With Range("B17:B190")
          .FormatConditions.Add Type:=xlTextString, String:="Company", TextOperator:=xlContains
          .FormatConditions(.FormatConditions.Count).SetFirstPriority
          .FormatConditions(1).StopIfTrue = True
          With .FormatConditions(1).Interior
               .PatternColorIndex = xlAutomatic
               .Color = 14734864
               .TintAndShade = 0
          End With
          
          .FormatConditions.Add Type:=xlTextString, String:="Syndicate", TextOperator:=xlContains
          .FormatConditions(.FormatConditions.Count).SetFirstPriority
          .FormatConditions(1).StopIfTrue = True
          With .FormatConditions(1).Interior
               .PatternColorIndex = xlAutomatic
               .Color = 65535
               .TintAndShade = 0
          End With
          
          .FormatConditions.Add Type:=xlTextString, String:="Bermuda", TextOperator:=xlContains
          .FormatConditions(.FormatConditions.Count).SetFirstPriority
          .FormatConditions(1).StopIfTrue = True
          With .FormatConditions(1).Interior
               .PatternColorIndex = xlAutomatic
               .ThemeColor = xlThemeColorAccent4
               .TintAndShade = 0.399945066682943
          End With
    End With
    
    With Range("C18:C190")
          .FormatConditions.Add Type:=xlExpression, Formula1:="=J18=""Red"""
          .FormatConditions(.FormatConditions.Count).SetFirstPriority
          .FormatConditions(1).StopIfTrue = False
          With .FormatConditions(1).Interior
               .PatternColorIndex = xlAutomatic
               .Color = 8420607
               .TintAndShade = 0
          End With
          
          .FormatConditions.Add Type:=xlExpression, Formula1:="=J18=""Amber"""
          .FormatConditions(.FormatConditions.Count).SetFirstPriority
          .FormatConditions(1).StopIfTrue = False
          With .FormatConditions(1).Interior
               .PatternColorIndex = xlAutomatic
               .Color = 49407
               .TintAndShade = 0
          End With
          
          .FormatConditions.Add Type:=xlExpression, Formula1:="=J18=""Green"""
          .FormatConditions(.FormatConditions.Count).SetFirstPriority
          .FormatConditions(1).StopIfTrue = False
          With .FormatConditions(1).Interior
               .PatternColorIndex = xlAutomatic
               .Color = 13434777
               .TintAndShade = 0
          End With
     End With
    
     With Range("B193:B300")
          .FormatConditions.Add Type:=xlTextString, String:="EU Corporate", TextOperator:=xlContains
          .FormatConditions(.FormatConditions.Count).SetFirstPriority
          .FormatConditions(1).StopIfTrue = True
          With .FormatConditions(1).Interior
               .PatternColorIndex = xlAutomatic
               .Color = 10092288
               .TintAndShade = 0
          End With
    
          .FormatConditions.Add Type:=xlTextString, String:="Syndicate", TextOperator:=xlContains
          .FormatConditions(.FormatConditions.Count).SetFirstPriority
          .FormatConditions(1).StopIfTrue = True
          With .FormatConditions(1).Interior
               .PatternColorIndex = xlAutomatic
               .Color = 65535
               .TintAndShade = 0
          End With
        
          .FormatConditions.Add Type:=xlTextString, String:="Company", TextOperator:=xlContains
          .FormatConditions(.FormatConditions.Count).SetFirstPriority
          .FormatConditions(1).StopIfTrue = True
          With .FormatConditions(1).Interior
               .PatternColorIndex = xlAutomatic
               .Color = 14734864
               .TintAndShade = 0
          End With
     End With
     
        Range("M16").Select
        Application.ScreenUpdating = True
        Application.EnableEvents = True
          Columns.AutoFit
          Range("A15").Select
      End Sub

**nickmax1** · 12-21-2012, 09:50 AM

thanks Olaf,

it seems to run a little quicker, about a minute on my machine. It was about 2 minutes before that.

**Jacc** · 12-21-2012, 09:48 AM

The macrorecorder is not that smart. Apart from using Select all the time it also records all properties even though just one or two has been changed.
Since you start by clearing the format on the whole range you can delete all the formatting commands that just sets the values to their defaults.

The first bunch of lines looking like this:

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

could be changed into something like this:

Sub FormattingGlobal()
Dim x As Range

Application.EnableEvents = False
Application.ScreenUpdating = False
    
    With ActiveSheet.Range("A1:K300")
        .FormatConditions.Delete
    End With
    
    With Range("A17:K190")
        With .Font
            .Name = "Arial"
            .Size = 9
        End With
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
    End With
    
    Range("A17:A190").VerticalAlignment = xlCenter

Note also the dim x statement. That tells VBA that I'm gonna use a variable named x and that it will contain a range. This saves a little bit of memory and speeds things up just a little bit.
I haven't tested this code and I may have deleted some rows that were not the defaults but hopefully it will give you some leads anyway.

**nickmax1** · 12-21-2012, 10:04 AM

thanks Jacc that shaves of a bit of time as well getting rid of the pointless macrorecorder code.

Formatting sheets with macro running very slowly

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Formatting sheets with macro running very slowly

Re: Formatting sheets with macro running very slowly

Re: Formatting sheets with macro running very slowly

Re: Formatting sheets with macro running very slowly

Re: Formatting sheets with macro running very slowly

Re: Formatting sheets with macro running very slowly

Re: Formatting sheets with macro running very slowly

Re: Formatting sheets with macro running very slowly

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions