Avoiding Having To Activate A Worksheet To Allow VBA Coded Formatting "Behind The Scenes"

[Ark68]

I have a question, which is likely of a basic understanding of how Excel works.

I am working on a VBA project.
From worksheet one, the user initiates VBA code (through a macro enabled shape). Part of this code changes the formatting of a range of cells on another worksheet (ws_master).

Sub GUI_Chg_Submit2()
    ui1 = MsgBox("This commits the current schedule for " & Format(n_date, "dddd mmm-dd") & Chr(13) & "to the master schedule after accuracy has been checked." & Chr(13) & Chr(13) & "Do you wish to continue?", vbQuestion + vbYesNo, "COMMITMENT")
    If ui1 = vbNo Then Exit Sub
   'transfer to master
        
        With ws_master
            'find date row
            Stop
            .Unprotect
            ... some code
                
                .Range(Cells(d_row, bcn), Cells(d_row, bcn + 4)).Font.Color = RGB(226, 107, 10)
                
            ... more code
            
        End With

End Sub

The line in red is giving me the error "Method 'Range of object '_Worksheet' failed." error.

This does not happen if I step through the code with worksheet ws_master visible, ie I switch sheets from worksheet one to ws_master. If I step through the code with sheet one visible, I get the error.

What must I add to my code, or change in my code, to avoid having to "activate"(?) ws_master first? I'd prefer all of this to happen behind the scenes from worksheet one. I don't want the user to be able to see ws_master, so not only do I want to take focus of it in the procedure, I also want to hide it eventually.

 

[RoryA]

This comes up frequently. You need to qualify the Cells calls with the same worksheet:

.Range(.Cells(d_row, bcn), .Cells(d_row, bcn + 4)).Font.Color = RGB(226, 107, 10)

Note the dots before both Cells words.

 

[Ark68]

Disregard. I found my error.
(Do posters have the ability to delete posts when this happens?)

 

[Ark68]

Haha ... thanks Rory. Just as you were typing your response I found that error.