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

感谢任何建议！

在玩弄了一下之后，我意识到我的问题不在于将条件格式公式应用于单元格范围，而是实际上当我单击下拉列表以过滤颜色时(在代码之后运行并应用了条件格式)过滤器下拉框需要永远出现(我假设是因为当时计算的所有公式?).知道如何解决这个问题吗?我已经尝试过@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屋！