CODEWITHSUNDEEP
vbaexcelexcel-formulaconditional-formatting
CaffeinatedMike
CaffeinatedMike

Reputation: 1607

How to apply conditional formatting formula to large range faster

I created a conditional formatting formula

=AND(SUMPRODUCT(($A$2:$A$" & lastRow & "=$A2)*($CT$2:$CT$" & lastRow & "=$CT2)*($CU$2:$CU$" & lastRow & "=$CU2)*($CV$2:$CV$" & lastRow & "=$CV2)*($CW$2:$CW$" & lastRow & "=$CW2))>1,$CT2 <> """")"

To find & highlight duplicate upcharges based on multiple criteria: Product's XID (Column A), Upcharge Criteria 1 (Column CT), Upcharge Criteria 2 (Column CU), Upcharge Type (Column CV), and Upcharge Level (Column CW). The formula works like a charm highlighting upcharges that are duplicates; however, much of the time I have to apply it to a large number of rows (upwards of 15000) and it takes 10+ minutes to apply the conditional format formula. I was curious if there is a quicker way of applying this formula to that many cells. My entire code for reference is

'File Complete, highlights duplicate upcharges for products and skips over blank upcharge rows
Sub dupUpchargeCheck()

Dim lastRow As Integer
lastRow = ActiveSheet.Cells(Rows.Count, "CS").End(xlUp).Row
ActiveSheet.Range("CS2:CS" & lastRow).Select
With ActiveSheet.Range("CS2:CS" & lastRow)
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(SUMPRODUCT(($A$2:$A$" & lastRow & "=$A2)*($CT$2:$CT$" & lastRow & "=$CT2)*($CU$2:$CU$" & lastRow & "=$CU2)*($CV$2:$CV$" & lastRow & "=$CV2)*($CW$2:$CW$" & lastRow & "=$CW2))>1,$CT2 <> """")"
    .FormatConditions(.FormatConditions.Count).Interior.ColorIndex = 3
End With

End Sub

Any advice is appreciated!

EDIT:

After toying around a bit, I've realized my problem isn't with the application of the conditional formatting formula to the range of cells, but actually when I click the drop down to filter on the color (after the code is ran and the conditional formatting is applied) it takes forever for the filter dropdown box to appear (I assume because all of the formulas calculating at that time?). Any idea how I can get around that issue? I've tried one of @Nate suggestions of calcState = Application.Calculation, placing it right before the line where I filtered (via vba) in hopes that it would stop the calculations from running as the system attempted to show the filter box, but it still takes forever. With the addition Application.ScreenUpdating = False the processing time takes slightly less time (timed around 551 seconds for 15000 rows). I'm afraid that might be the best I'll be able to get it unless someone else has any suggestions?

Upvotes: 0

Views: 1438

Answers (2)

Fadi
Fadi

Reputation: 3322

This didn't help me before, but I hope it help you as this link said :

This is particularly useful when condition is applied over a large range as performance can be slow in these cases.

ActiveSheet.EnableFormatConditionsCalculation = False

'.....

ActiveSheet.EnableFormatConditionsCalculation = True

Maybe this not work on MAC. ActiveSheet.EnableFormatConditionsCalculation Not Supported in VBA for Excel 2011 on the Mac

Upvotes: 0

user4736025
user4736025

Reputation:

Try turning off some Excel features before your code then turning them back on when it is done.

' turn off unnecessary excel features, put before your code
screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
displayPageBreakState = ActiveSheet.DisplayPageBreaks
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

Then

' Turn features back on
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState

Upvotes: 1

Related Questions