Intermittent 1004 error trying to set font color

[OaklandJim]

Team Mr. EXCELlent

I am flummoxed. I have some code that merely grays out some data fields (cells) in a worksheet so user does not try to use irrelevant inputs. But SOMETIMES my seemingly simple code gives me a 1004 Application-defined or Object-defined error. I cannot figure out why my code works sometimes and not others.

Here is a portion of the code as an example. In the Control worksheet are the cells to gray out. I know that the range name is a bit clunky but that is not the issue. It seems to be related to how I am setting the font color. I added the second With statement -- for the range itself -- to try to isolate the issue. Indeed the 1004 error is from code .Font.ColorIndex = 15

    With [Control]

'This reports the correct address of the range whose font color I am trying to change.
Debug.Print .Range("bVault_Files_Thisworkbook_Directory").Offset(0, -1).Resize(1, 2).Address

'       Vault Log folder same as Thisworkbook directory flag.
        With .Range("bVault_Files_Thisworkbook_Directory").Offset(0, -1).Resize(1, 2)
            .Font.ColorIndex = 15 '<= the offending code
        End With
        
    End With

The debug.print statement yields this result: $B$25:$C$25, so I know that the range itself is valid. The worksheet is protected using the UserInterfaceOnly setting.

What simple concept/mistake am I missing?

 

[Fluff]

Whilst I've never used UserInterfaceOnly I have heard that it can be temperamental and does not work with everything.

 

[OaklandJim]

Maybe this is my issue? From StackOverflow:

The status UserInterfaceOnly:=TRUE is unfortunately not stored in the file - therefore, if you reopen the file, it will be fully protected. Either use the Workbook_Open event to reapply this protection status - or unprotect and then reprotect the worksheet in your VBA code directly

 

[OaklandJim]

OK, so unprotecting then reprotecting UserInterfaceOnly when the workbook is opened seems to have fixed the issue. Thank you Fluff for the assist!

 

[Fluff]

Glad we could help & thanks for the feedback.