如何将条件格式化公式应用于较大范围更快 [英] 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 <> """")"

根据多个条件查找并突出显示重复的收费：
产品的XID（列A），上升标准1（列CT），上升标准2（CU），升压类型（列CV）和升压级（列CW）。该公式的作用就像一个突出显示重复的充电的魅力;很多时候我必须将它应用到大量行（15000以上），并且需要10多分钟的时间来应用条件格式公式。如果有更快的方法将这个公式应用到这个公式，我很好奇我的整个代码参考是

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

任何建议不胜感激！

编辑： strong>

在一段时间内，我意识到我的问题不是将条件格式化公式应用于单元格的范围，而是实际上当我单击下拉菜单以过滤颜色（运行代码并应用条件格式后），将永久显示过滤器下拉框出现（我假设因为当时计算的所有公式）。任何想法如何解决这个问题？我尝试过一个@Nate建议
calcState = Application.Calculation ，将它放在我过滤的行（通过vba）之前，希望它系统将尝试显示过滤器框时，会停止计算运行，但仍然会持续运行。加上 Application.ScreenUpdating = False 处理时间需要稍微少一点的时间（15000行的时间约为551秒）。恐怕这可能是最好的，除非别人有任何建议，否则我可以得到它。

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?

推荐答案

尝试在您的代码之前关闭一些Excel功能，然后在完成后重新打开。

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

然后

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

这篇关于如何将条件格式化公式应用于较大范围更快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！