userinterfaceonly:=true doesn't seem to allow VBA changes to conditional formatting?

Question

I'm running a piece of code via the Worksheet_Change Event and I have it working with a call to an unprotect sub at the beginning of the event and a matching call to a protect sub at the end of the event. This works as expected.

I'm trying to work with setting the protection to userinterfaceonly:=true in the workbook open event to negate the need to unprotect and reprotect each time the change event fires (more just to explore the functionality than anything else). Problem is that the code to change conditional formatting on a locked range inevitably errors if the worksheet is protected (even with UserInterfaceOnly = True) although it works fine and as expected if the worksheet is unprotected either manually or by VBA unprotecting the sheet before that line and re-protecting after that line.

I've played around a bit and it seems to error on anything to do with changing conditional formatting on a locked cell. I wonder if changing conditional format on a locked range while a sheet is protected isn't allowed under the scope of UserInterfaceOnly? If anyone knows if this is a limitation and if there are any other limitations to this argument, it would be great to know.

In my 'This Workbook' module I have:

Private Sub Workbook_open()

ActiveSheet.Protect userinterfaceOnly:=True

End Sub

In my worksheet's module, I have the below - the FormatConditions.Delete line errors with a runtime error 1004: Application or Object Defined Error when that Case resolves to true.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$6" Then
    Application.ScreenUpdating = False
        Select Case Range("D6")
            Case "Select Function"
                Range("F6").Value = ("")
                Range("H4:I4").Select
                    Selection.FormatConditions.Delete <<<<<<THIS LINE ERRORS
                    Selection.ClearContents
                Call DeleteButtons
                Call HideAll
                Range("D6").Select
            Case "Goods In & Redelivery"
                Range("F6").Value = ("EXPLANATORY TEXT")
                Call DeleteButtons
                Range("D10:F10").ClearContents
                Call UnHideAll
                Call HideCollection
                Call FillDelivery
                Call GIRButtons
                Range("D10").Select
            Case "Collection & Redelivery"
                Range("F6").Value = ("EXPLANATORY TEXT")
                Call DeleteButtons
                Call UnHideAll
                Call HideGoodsIn
                Call ClearDelivery
                Call CRButtons
                Range("H4").Select
            Case "Delivery Only"
                Range("F6").Value = ("EXPLANATORY TEXT")
                Call DeleteButtons
                Call UnHideAll
                Call HideGoodsInCollection
                Call ClearDelivery
                Call DelButtons
                Range("H4").Select
        End Select
    Application.ScreenUpdating = True
    End If
End Sub

Answer 1

Along with .Protect UserInterfaceOnly:=True, use AllowFormattingCells:=True. This allows conditional formatting to modify protected cells.

Answer 2

I definitely consider this as an Excel bug as the UserInterfaceOnly option is intended exactly to allow vba code to change the worksheet without having to unprotect and protect the sheet all the time, which has numerous disadvantages (Some reasons why not to do this in this link).

While Anthony's answer can be used as a workaround, it opens the possibility for the user to change the worksheet cells formats even when it is protected which, in my case, is not acceptable.

I struggled until I found an acceptable solution. It is still a workaround, but an acceptable one.

The workaround:

The workaround consists in creating a set of "templates" of cells with the desired formats (including conditional formatting) in another region of the spreadsheet (that can, after the design phase of your worksheet, be hidden or reside in another worksheet) and use Range.Copy and Range.PasteSpecial whenever you want to apply their format in visible regions of the spreadsheet (which specific portion of the templates to copy from can be defined in your vba logic). Of course, as usual, Excel will do its very good job of adjusting the conditional formatting formulas to the copy destination (unless you anchor the addresses with $ like $A$1, that may be useful in specific cases). (please read the side effects below to circumvent them)

OBS: Range.Copy <Another range as a parameter> between different worksheets also violates the worksheet protection regardless of the UserInterfaceOnly option being used (in the same worksheet this works fine). If you want to copy between different worksheets with protection, split the operation in two: first use Range.Copy and then Range.PasteSpecial xl<whatever> (yep, another excel worksheet protection bug)

Side benefit

A side benefit of this approach it is that you can use the Excel's interface to design the template instead of having to code using Range.FormatConditions.<whatever>. This can be very helpful when dealing with patterns, colors, etc. and also helps reducing the amount of VBA code.

OBS: Note that for code simplification I even use named ranges in the templates cells so that the Range.Copy calls get more elegant in the code, like Range("ATemplateNamedRange").Copy.

Side effects

Depending on the Office version you use and its applied patches, Excel can make a real mess with C&P of cells containing conditional formatting, resulting in the accumulation and duplication of rules. Since using Range.ConditionalFormatting.Delete before pasting would violate sheet protection because of the mentioned bug, the only workaround possible is to use Range.ClearFormatting (which correctly respects the UserInterfaceOnly option) in the destination before pasting. Yes, this will wipe all the formatting, but you can recover it from the template (and clever usage of Application.ScreenUpdating = <true/false>)

Other weird effects regarding conditional formatting

Also depending on the Office version, deleting cells (not clearing, but actually deleting) messes conditional formatting rules ranges (this doesn't even depend on what region is being deleted, which can be completely unrelated to the cells with the conditional formatting). Excel tries to be smart and unwantedly split/merge ranges (making their maintenance bearably impossible). Clever usage of C&P during design instead of delete/insert is the workaround to avoid this.