使用VBA设置条件格式的陷阱

完美Excel · 公众号 · Excel · 2024-09-20 05:32

正文

‍

学习Excel技术，关注微信公众号：

excelperfect

标签：VBA，条件格式

Excel中的条件格式是一个非常有用的工具，然而，如果在条件格式工作表中复制了许多单元格，那么这些单元格的条件格式也将被复制。这本身当然很好，但Excel并不总是处理得很好。Excel可能会错误地拆分条件格式区域，这可能会导致文件中出现大量额外的条件格式规则，导致文件大小增加并降低了工作表速度，甚至可能导致Exce崩溃。

如果在具有条件格式的工作表中复制、移动、剪切和粘贴大量的单元格，那么明智的做法可能是先删除条件格式，然后在激活该工作表时再恢复。

使用VBA设置条件格式本身并不难。下面是一个使用宏录制器录制的代码示例，首先删除当前的条件格式，然后重新设置。

Private Sub Worksheet_Activate() With ActiveWorkbook.Sheets("excelperfect").Range("B3:C100")   .FormatConditions.Delete   .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(OR($B3<>"""",$C3<>""""),ISODD(ROW()))"   .FormatConditions(1).Interior.Color = vbMagenta   .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(OR($B3<>"""",$C3<>""""),ISEVEN(ROW()))"   .FormatConditions(2).Borders(xlBottom).LineStyle = xlContinuous   .FormatConditions(2).Borders(xlBottom).Color = vbMagenta End WithEnd Sub

显然，代码在工作表excelperfect中单元格区域B3:C100设置了条件格式：如果奇数行中单元格不为空则其背景色设置为洋红色，如果偶数行中单元格不为空则将添加其底部边框线并设置为洋红色。

然而，如果对工作表设置了保护，那么运行上述代码就会发生错误。此时，可以先设置代码来暂时撤销工作表保护，然后再在代码末尾恢复工作表保护，代码如下：

Private Sub Worksheet_Activate()  ActiveWorkbook.Sheets("excelperfect").Unprotect Password:="123"  With ActiveWorkbook.Sheets("excelperfect").Range("B3:C100")    .FormatConditions.Delete    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(OR($B3<>"""",$C3<>""""),ISODD(ROW()))"    .FormatConditions(1).Interior.Color = vbMagenta    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(OR($B3<>"""",$C3<>""""),ISEVEN(ROW()))"    .FormatConditions(2).Borders(xlBottom).LineStyle = xlContinuous    .FormatConditions(2).Borders(xlBottom).Color = vbMagenta End With ActiveWorkbook.Sheets("excelperfect").Protect Password:="123", UserInterFaceOnly:=TrueEnd Sub

下面的代码更通用：

Private Sub Worksheet_Activate() Dim sLocalFormula1 As String, sLocalFormula2 As String Application.ScreenUpdating = False With ActiveWorkbook.Sheets("excelperfect")   .Unprotect Password:="123"   Application.EnableEvents = False   .Range("A2").Formula = "=AND(OR($B3<>"""",$C3<>""""),ISODD(ROW()))"   sLocalFormula1 = .Range("A2").FormulaLocal   .Range("A2").Formula = "=AND(OR($B3<>"""",$C3<>""""),ISEVEN(ROW()))"   sLocalFormula2 = .Range("A2").FormulaLocal   .Range("A2").ClearContents   .Range("B3").Select   Application.EnableEvents = True End With With ActiveWorkbook.Sheets("WorksheetsVBA").Range("B3:C100")   .FormatConditions.Delete   .FormatConditions.Add Type:=xlExpression, Formula1:=sLocalFormula1   .FormatConditions(1).Interior.Color = vbMagenta   .FormatConditions.Add Type:=xlExpression, Formula1:=sLocalFormula2   .FormatConditions(2).Borders(xlBottom).LineStyle = xlContinuous   .FormatConditions(2).Borders(xlBottom).Color = vbMagenta End With ActiveWorkbook.Sheets("excelperfect").Protect Password:="123", UserInterFaceOnly:=True Application.ScreenUpdating = TrueEnd Sub

注：本文学习整理自worksheetsvba.com，供有兴趣的朋友研究参考。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。