VBA Help

T

THRASHER69

Board Regular
Joined
Mar 29, 2012
Messages
200
Hello,

I'm hoping some one can help me out. The part of my code listed below really seems to slow my macro down. Anyone know a better way to write this to make it run faster?

VBA Code: 
    Set iRow = Range("I2", Range("I2").End(xlDown))

    With iRow
        .FormatConditions.Add Type:=xlExpression, Formula1:= _ 
           "=IF($I2<$G2,TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = vbYellow
                .TintAndShade = 0
            End With
        End With
    End With

    Set iRow1 = Range("J2", Range("J2").End(xlDown))

    With iRow1
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($J2<$G2,TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = vbYellow
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow2 = Range("C2", Range("C2").End(xlDown))

    With iRow2
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF(N2=""Released to Warehouse"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 22
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow3 = Range("E2", Range("E2").End(xlDown))

    With iRow3
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF(N2=""Released to Warehouse"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 22
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow4 = Range("N2", Range("N2").End(xlDown))

    With iRow4
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF(N2=""Released to Warehouse"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 22
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow5 = Range("C1")

    With iRow5
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(N:N,""Released to Warehouse"")"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 22
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow6 = Range("E1") 

    With iRow6
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(N:N,""Released to Warehouse"")" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 22
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow7 = Range("D2", Range("D2").End(xlDown)) 

    With iRow7
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($N2=""Staged/Pick Confirmed"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 4
                .TintAndShade = 0
            End With
        End With
    End With
    
     Set iRow8 = Range("N2", Range("N2").End(xlDown))

    With iRow8
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($N2=""Staged/Pick Confirmed"",TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 4
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow9 = Range("C2", Range("C2").End(xlDown))

    With iRow9
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($O2=TODAY(),TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 43
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow10 = Range("O2", Range("O2").End(xlDown)) 

    With iRow10
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($O2=TODAY(),TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 43
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow11 = Range("A2", Range("A2").End(xlDown)) 

    With iRow11
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($P2<>"""",TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 35
                .TintAndShade = 0
            End With
        End With
    End With
    
    LastRow3 = Cells(Rows.Count, "F").End(xlUp).Row 
    
    Set iRow12 = Range("P2:P" & LastRow3)

    With iRow12
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($P2<>"""",TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 35
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow13 = Range("B1")

    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""1320 EAST LOS ANGELES AVENUE,SHAFTER,CA,93263,US"")"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""10353 RICHMOND AVENUE,HOUSTON,TX,77042,US"")"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""19417 COLOMBO STREET,BAKERSFIELD,CA,93308,US"")" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""2040 OREGON STREET,ODESSA,TX,79764,US"")"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""1444 NORTH DERRICK DRIVE,CASPER,WY,82604,US"")" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""10906 FM 2920 ROAD,TOMBALL,TX,77375,US"")" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""21255 LA HIGHWAY 1 SOUTH,PLAQUEMINE,LA,70764,US"")" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""9870 EAST 30TH STREET,INDIANAPOLIS,IN,46229,US"")"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""8708 WEST LITTLE YORK,SUITE 100,HOUSTON,TX,77040,US"")" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow14 = Range("B2", Range("B2").End(xlDown))

    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""1320 EAST LOS ANGELES AVENUE,SHAFTER,CA,93263,US"",TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""10353 RICHMOND AVENUE,HOUSTON,TX,77042,US"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""19417 COLOMBO STREET,BAKERSFIELD,CA,93308,US"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""2040 OREGON STREET,ODESSA,TX,79764,US"",TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""1444 NORTH DERRICK DRIVE,CASPER,WY,82604,US"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""10906 FM 2920 ROAD,TOMBALL,TX,77375,US"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""21255 LA HIGHWAY 1 SOUTH,PLAQUEMINE,LA,70764,US"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""9870 EAST 30TH STREET,INDIANAPOLIS,IN,46229,US"",TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""8708 WEST LITTLE YORK SUITE 100,HOUSTON,TX,77040,US"",TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With

Thank you
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
I was just looking for a better way to write the code to speed things up. This part of the code seems to really slow things down.
 
Upvote 0
That part I understand, but I can’t help you if I don’t know what spreadsheet looks like, or what the end goal of the macro is. All it appears to do is apply conditional formatting to an arbitrary selection of cells - could it be that there is too much conditional formatting being applied to the sheet? That’s a common cause.
 
Upvote 0
Yes, there is a lot of conditional formatting going on and I know that slows things down but it really helps me to quickly identify a lot of different things on a very large spreadsheet. Is there a better way to help me identify things other then adding the conditional formatting? Or maybe just a better way to write the code to do the formatting that will make it quicker? There may not be a better way. Just looking for ideas from people way smarter than me :biggrin:
 
Upvote 0
One thing that may help is to suppress all screen updates until the very end.
You can do that by placing this line of code at the very top of your code:
VBA Code: 
Application.ScreenUpdating = False
and then placing this line at the very end of your code:
VBA Code: 
Application.ScreenUpdating = True
 
Upvote 0
Thank you for the advice. To help with speed I actually have the following at the beginning of my code:
VBA Code: 
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.PrintCommunication = False
and the following at the end of my code:
VBA Code: 
Application.PrintCommunication = True
Application.EnableEvents = True
Application.DisplayStatusBar = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
 
Upvote 0
Looks like you pretty much got all the bases covered then.
I guesses it boils down to the sheer volume of data and Conditional Formatting formulas you are applying (you have a bunch of conditions being applied).

One option is to remove/replace the Conditional Formatting with VBA code instead (you could use Worksheet_Change event procedure code, provided that all your data is hard-coded data that is changed manually, and not formulas or links).

Another consideration may be to make "on-demand" Conditional Formatting, instead of doing all of the Conditional Formatting on all of the columns initially. So, initially, no Conditional Formatting is applied, and then you create separate VBA procedures to turn on/turn off each Conditional Formatting option separately, so they only run the one they need when they need it.
I don't know if that is feasible or even makes sense for what you are trying to accomplish, but is just a thought you may want to consider.
 
Upvote 0
If you are running that code only once for that that worksheet, then there is not much to be done. However if you are running it more than once without deleting old CF, you are likely clogging the sheet with multiple sets of CF rules and that will cause a slowdown.
 
Upvote 0
Joe4,
Would you mind showing me some sample code on what you are talking about with Worksheet_Change event procedure?

rlv01,
The code runs only once for the sheet ran fresh daily.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
highlight numbers
Replies
7
Views
451
MARK858
MARK858
K
  • Question Question
Highlight line question
Replies
2
Views
434
kerm007
K
J
  • Question Question
Conditional formatting Coloring due dates
Replies
4
Views
97
JAZ91
J
Q
  • Solved
Using VBA instead of Conditional formatting
2
Replies
12
Views
689
Micron
M
S
  • Question Question
Run VBA code from specific sheet till the end of workbook
Replies
7
Views
244
Alex Blakenburg
A

Forum statistics

Threads
1,223,532
Messages
6,172,878
Members
452,486
Latest member
standw01

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top