VBA script not running with blocked cells (not the ones the vba script is using)

Hi,

so I'm running a vba script which should change the color of a cell after the user has been modifying the sheet.
The cells the script is coloring in is not protected, but other cells in the same row. (7+)

Without protection the script is running fine, with I'm running into an error (request to debug).
I have no clue why.

Option Explicit

Public Const FirstRowNumber As Long = 16

Sub ColorData(SheetName As String)
   If SheetName = vbNullString Then Exit Sub
   Dim ws As Worksheet, SheetExist As Boolean, I As Long, LastUsedRow, DayString As String
   
   For Each ws In Worksheets
      If ws.Name = SheetName Then
         SheetExist = True
         Exit For
      End If
   Next ws
   If SheetExist = False Then Exit Sub
   
   LastUsedRow = ws.Cells(ws.Rows.Count, 11).End(xlUp).Row

   'Schleife zum Zellen entf?rben
   For I = FirstRowNumber To LastUsedRow
       ws.Range(ws.Cells(I, 3), ws.Cells(I, 6)).Interior.ColorIndex = 2
       ws.Range(ws.Cells(I, 3), ws.Cells(I, 6)).Interior.Pattern = xlSolid
       DayString = ws.Cells(I, 2).Value
       
       Select Case DayString
           Case "":    ws.Range(ws.Cells(I, 3), ws.Cells(I, 6)).Interior.ColorIndex = 36
           Case "HO":  ws.Range(ws.Cells(I, 3), ws.Cells(I, 6)).Interior.ColorIndex = 36
           Case "ho":  ws.Range(ws.Cells(I, 3), ws.Cells(I, 6)).Interior.ColorIndex = 36
           Case "DA":  ws.Range(ws.Cells(I, 3), ws.Cells(I, 6)).Interior.ColorIndex = 36
           Case "da":  ws.Range(ws.Cells(I, 3), ws.Cells(I, 6)).Interior.ColorIndex = 36
           Case "U":   ws.Range(ws.Cells(I, 3), ws.Cells(I, 6)).Interior.Pattern = xlLightUp
           Case "K":   ws.Range(ws.Cells(I, 3), ws.Cells(I, 6)).Interior.Pattern = xlLightUp
           Case "S":   ws.Range(ws.Cells(I, 3), ws.Cells(I, 6)).Interior.Pattern = xlLightUp
           Case "GT":  ws.Range(ws.Cells(I, 3), ws.Cells(I, 6)).Interior.Pattern = xlLightUp
           Case "Sa":  ws.Range(ws.Cells(I, 3), ws.Cells(I, 6)).Interior.Pattern = xlLightUp
           Case "So":  ws.Range(ws.Cells(I, 3), ws.Cells(I, 6)).Interior.Pattern = xlLightUp
           Case Else:  ws.Range(ws.Cells(I, 3), ws.Cells(I, 6)).Interior.Pattern = xlLightUp
      End Select
   Next I
End Sub

I can and will provide any necessary data anybody needs to solve this puzzle.
Hope somebody can help me,
Edward

Originally Posted by davesexcel

Unprotect the sheet at the beginning of your code, then protect it again at the end of the code.

Changed it to

  For I = FirstRowNumber To LastUsedRow
       ws.Range(ws.Cells(I, 7), ws.Cells(I, 16)).Locked = False
       ws.Range(ws.Cells(I, 3), ws.Cells(I, 6)).Interior.ColorIndex = 2
       ws.Range(ws.Cells(I, 3), ws.Cells(I, 6)).Interior.Pattern = xlSolid
       DayString = ws.Cells(I, 2).Value
       ws.Range(ws.Cells(I, 7), ws.Cells(I, 16)).Locked = True

, but still getting the same error.

Attachment 814919

Originally Posted by davesexcel

Where are you unprotecting the sheet?

Search for "excel vba unprotect sheet"

I thought it was:

ws.Range(ws.Cells(I, 7), ws.Cells(I, 16)).Locked = False

But after using your searchterm I found this:

Sheets("Sheet1").Unprotect Password:="ADSBP"

Do I need only the 2nd one, or both?

Originally Posted by rorya

Did you select the option to allow formatting cells when protecting the sheet?

How do I do that? I'm quite a noob with vba and just clobbering this together.

Originally Posted by rorya

Did you protect the sheet in code originally? If not, just check that option when protecting the sheet in the UI. If you did do it in code, it's like this:

activesheet.protect password:="whatever", allowformattingcells:=true

I did it using the GUI.
Might that be the problem? Problem is, I need only to protect some cells, so nobody is overwriting the formulas.

Originally Posted by rorya

When you protect the sheet in the UI, one of the options you can set is to allow formatting cells. Then your code should just work as is.

And that was the fast solution
Thank you very much.