Announcement

Collapse
No announcement yet.

Optimize VBA Code for Speed?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Optimize VBA Code for Speed?



    I am using the following code (see code example at bottom of this post) to change some cell formatting based on the value of a cell linked to a combo box. I first tried using named ranges but that failed miserably (it made the formatting changes but only to the Print Area and it was S-L-O-W). In any case, the code below works but still runs pretty slowly (3-5 seconds to change the formats for 8 cells) and I am trying to find a way to make it run lickity-split fast. Any help would be much appreciated.
    SNIPPET OF CODE
    Code:
    Private Sub ComboBox1_Change()
    Dim myCell As Range
    Dim cell As Range
    
    Set myCell = Range("$M$2")
    
        For Each cell In Range("$B$2:$K$3")
            If myCell.Value = "SiteA" Then
                cell.Interior.ColorIndex = 25
                cell.Font.ColorIndex = 2
                cell.Font.Bold = True
                
            ElseIf myCell.Value = "SiteB" Then
                cell.Interior.ColorIndex = 14
                cell.Font.ColorIndex = 2
                cell.Font.Bold = True
                
            ElseIf myCell.Value = "SiteC" Then
                cell.Interior.ColorIndex = 53
                cell.Font.ColorIndex = 2
                cell.Font.Bold = True
             End If
    
        Next cell
    End Sub
    END SNIPPET
    Thanks,
    Omomyid

  • #2
    This may or may not help, but Select Case will typically run faster than If... Then code. Also, With ... End With will provide more speed.. Something like this? And maybe a couple other changes to accommodate this.

    Code:
    Sub Test()
        Dim myCell As Range
        Dim cell As Range
    
        Set myCell = Range("$M$2")
    
    
        For Each cell In Range("$B$2:$K$3")
            Select Case myCell.Value
                Case myCell.Value = "SiteA"
                    With cell
                        .Interior.ColorIndex = 25
                        .Font.ColorIndex = 2
                        .Font.Bold = True
                    End With
                Case myCell.Value = "SiteB"
                    With cell
                        .Interior.ColorIndex = 14
                        .Font.ColorIndex = 2
                        .Font.Bold = True
                    End With
    
                Case myCell.Value = "SiteC"
                    With cell
                        .Interior.ColorIndex = 53
                        .Font.ColorIndex = 2
                        .Font.Bold = True
                    End With
    
                Case myCell.Value = "SiteD"
                    With cell
                        .Interior.ColorIndex = 5
                        .Font.ColorIndex = 2
                        .Font.Bold = True
                    End With
    
                Case myCell.Value = "SiteE"
                    With cell
                        .Interior.ColorIndex = 3
                        .Font.ColorIndex = 2
                        .Font.Bold = True
                    End With
                Case myCell.Value = "SiteF"
                    With cell
                        .Interior.ColorIndex = 37
                        .Font.ColorIndex = 2
                        .Font.Bold = True
                    End With
            End Select
        Next cell
    End Sub

    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt

    Old, slow, and confused - but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3.28.2008)

    Comment


    • #3
      Ok, thanks for the tip...unfortunately while there are no visible errors, the cell formatting is not changing when a new value is selected from the combo box.
      I am probably missing 'those couple other changes to accomodate this' since I am a VBA neophyte.

      -Omomyid

      Comment


      • #4
        Code:
        'The lines
        Case myCell.Value = "SiteE" 
        
        'Should be
        Case "SiteE"
        TJ
        Oh dear I need a beer
        Online Motorsport Game

        Comment


        • #5
          Tinyjack,

          Definitely works now, but runs just as slowly as the If/Then loops from the first iteration.
          Do you think it would run faster with a named range? If so, can you give me an example of how to code in the named range (which as stated in the original post I failed horribly with)

          Thanks,

          Omomyid

          Comment


          • #6
            Why use For Each? Why not just:

            Code:
            Range("$B$2:$K$3").Interior.ColorIndex = 25
            TJ
            Oh dear I need a beer
            Online Motorsport Game

            Comment


            • #7
              PERFECT!!!

              Thanks so much for your quick response and excellent advice!

              -Omomyid

              Comment


              • #8
                Final Code for Conditional Formatting on Combo Box Change

                Thought folks might find the final code solution for this question useful.
                It can be used to conditionally format cell ranges based on the change of a value selected in a combo box control. It should only be used if you have 5 or more items in the combo box, since the native conditional formatting in Excel can handle up to 4 cases (3 conditional formats plus one base format).
                Obviously, you can add as many cases as there are items in the drop down.

                Code:
                Private Sub ComboBox1_Change()
                    Dim myCell As Range
                    Dim myRng As Range
                    
                    Set myCell = Range("$M$2") 'Set myCell to the address of the combo box's linked cell
                    Set myRng = Range("$B$2:$K$3,$B$22:$K$23") 'This is the range to be modified
                
                        Select Case myCell.Value
                
                        Case "A" 'This is the case that you want to match
                            With myRng
                                .Interior.ColorIndex = 25 'range background color
                                .Font.ColorIndex = 2 'font color
                                .Font.Bold = True 'font weight
                            End With
                
                        Case "B"
                            With myRng
                                .Interior.ColorIndex = 14
                                .Font.ColorIndex = 2
                                .Font.Bold = True
                             End With
                             
                        Case "C"
                            With myRng
                                .Interior.ColorIndex = 53
                                .Font.ColorIndex = 2
                                .Font.Bold = True
                             End With
                        End Select
                End Sub
                -Omomyid

                Comment


                • #9


                  Hi Omomyid

                  Thanks fo posting your final code. When posting code could you please use the Code tags so others can easily copy paste or read your code. E.g

                  [ code]
                  Your Code
                  [ /code]

                  Less any space after [

                  You may find the links below of use also;

                  Efficient VBA Code Part 1

                  Efficient VBA Code Part 2

                  Efficient VBA Code Part 3

                  Comment

                  Working...
                  X