Pitfalls setting conditional format with VBA

Introduction

Conditional formatting in Excel is a very useful tool. However, if many cells are copied in a conditional formatting sheet, the conditional formatting of these cells will also be copied. In itself that is of course good, but Excel does not always handle this well. Conditional formatting ranges can be incorrectly split by Excel, which can cause lots of extra conditional formatting rules to end up in the file. This increases file size and slows down speed. It may even lead to Exce crashing.

If in a sheet with conditional formatting many cells will be copied, moved, cut and pasted, it may be wise to first remove the conditional formatting and then reset it when activating the sheet. This article discusses how this can be done, with particular attention to the problems that can play a role in this and what can be done about it.

The suggestions discussed here can be applied wherever problems / errors arise when setting up conditional formatting with VBA.

Set conditional formatting with VBA

Setting the conditional formatting with VBA is not very difficult in itself. A good starting point is often to manually set the conditional format first and then turn on the macro recorder, so that you can see which code is being generated.. Below is an example of code that gives the range in question a table-like look. It will first remove the current conditional formatting and then reset it. In this case the Worksheet_Activate event is used. A point of attention is that this event is not triggered on the 1st sheet that can be seen after a workbook has been opened. If necessary, this can be included in the Workbook_Open event.

Private Sub Worksheet_Activate()
    With ActiveWorkbook.Sheets("WorksheetsVBA").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
End Sub

Protected sheets

If the sheet is protected, an error message will follow with the preceding code on the line with .FormatConditions.Delete (error 1004: ‘Application-defined or operation-defined error’). That in itself makes sense, but this error also follows if UserInterFaceOnly=True. Normally with this setting you can change the sheet with VBA without removing the protection of the sheet. But apparently that doesn't work in this case. So on protected sheets the protection must first be removed and then put back again. The code then becomes:

Private Sub Worksheet_Activate()
    ActiveWorkbook.Sheets("WorksheetsVBA").Unprotect Password:="1234"
    With ActiveWorkbook.Sheets("WorksheetsVBA").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("WorksheetsVBA").Protect Password:="1234", UserInterFaceOnly:=True
End Sub

Non English Excel versions

But with non-English Excel versions the above code still produces an error (error 5:’Invalid procedure call or argument’), but now on the next line. The reason is that in this case the formulas must be entered as a local formula. This is very confusing, but normally the English formula should always be used in VBA (unless a conscious choice is made for FormulaLocal), but not here. For the Dutch Excel version, the code must then be:

Private Sub Worksheet_Activate()
    ActiveWorkbook.Sheets("WorksheetsVBA").Unprotect Password:="1234"
    With ActiveWorkbook.Sheets("WorksheetsVBA").Range("B3:C100")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=EN(OF($B3<>"""";$C3<>"""");IS.ONEVEN(RIJ()))"
        .FormatConditions(1).Interior.Color = vbMagenta
        .FormatConditions.Add Type:=xlExpression, Formula1:="=EN(OF($B3<>"""";$C3<>"""");IS.EVEN(RIJ()))"
        .FormatConditions(2).Borders(xlBottom).LineStyle = xlContinuous
        .FormatConditions(2).Borders(xlBottom).Color = vbMagenta
    End With
    ActiveWorkbook.Sheets("WorksheetsVBA").Protect Password:="1234", UserInterFaceOnly:=True
End Sub

Solution that works in all language versions

If a file must be able to work in any language version, the code needs to be further adjusted. First, the formula will be stored as an English formula in an empty cell of the worksheet. Then this formula is read in the local language and that formula is then used in the conditional format. Finally, the temporarily stored formula is removed from the cell. In this way, this solution works in all language versions. The final code then becomes:

Private Sub Worksheet_Activate()
    Dim sLocalFormula1 As String, sLocalFormula2 As String
    Application.ScreenUpdating = False
    With ActiveWorkbook.Sheets("WorksheetsVBA")
        .Unprotect Password:="1234"
        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 'bleek alleen nodig in Excel 2007, omdat anders de formules niet correct werden ingesteld
        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("WorksheetsVBA").Protect Password:="1234", UserInterFaceOnly:=True
    Application.ScreenUpdating = True
End Sub

Questions / suggestions

Hopefully this article has helped to understand the pitfalls that can exist when setting up conditional formatting with VBA. If you have any questions about this topic or suggestions for improvement, please post a comment below.