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?

 

[Candyman8019]

Try changing:

'       Vault Log folder same as Thisworkbook directory flag.
        With .Range("bVault_Files_Thisworkbook_Directory").Offset(0, -1).Resize(1, 2)

to

'       Vault Log folder same as Thisworkbook directory flag.
        With .Range("bVault_Files_Thisworkbook_Directory").Offset(0, -1).Resize(1, 2).address

 

[Candyman8019]

Scratch that...looked like it worked for me the first time and then not again...

 

[Candyman8019]

This worked:

With Range("bVault_Files_Thisworkbook_Directory").Offset(0, -1).Resize(1, 2)

Just got rid of the . before range.

 

[OaklandJim]

Thank you Candyman8019 for the prompt response.

Nope, that is not the right fix. If adding address as suggested then I am not using an object so get Compile Error "With object must be user-defined type, object or variant."

 

[OaklandJim]

If using your second suggestion how does Excel know which worksheet to look in? My code starts with the With [Control] statement to specify that the range object is on the Control worksheet.

 

[Candyman8019]

I'm feeling like this is more of a range issue. Is it possible that the range "bVault_Files_Thisworkbook_Directory" changes. For example, if it were ever to include column A and then you offset -1 column, it could result in this error.

 

[Fluff]

Is the sheet protected?

 

[OaklandJim]

No, that name and the cell it points to do not change. It is just a range name. And as shown above the Debug.Print statements reports the correct range address.

And, as I said, the issue is intermittent.

I had this clunkier code earlier that seemed to work...

    With [Control]

'       "Use this workbook" setting and label.
        With .Range("bVault_Files_Thisworkbook_Directory")
            .Value = True

            With .Offset(0, -1).Resize(1, 2).Font
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.249977111117893
            End With

        End With 'range

    End With '[Control]

...but I wanted to tighten it up so I went to the offending code.

I guess I'll just have to accept that I am doing something really basic to cause the error. ****!

 

[OaklandJim]

Fluff yes protected UserInterfaceOnly. I suppose I could try unprotecting first?