Object required error

[fvisions]

Boss made changes to my worksheet that locks and hides columns. I need to add the code to have cells AD1:AK1 colored and locked but all the rest of the cell in the column are unlocked for the end user. I am getting an object required error when I attempted to add the coded needed. I am not well educated in objects in vba.

Dim ws As Worksheet

With Worksheets("CWS")
.Range("B1").Value = "."
.Range("D1").Value = "."
.Range("H1").Value = "Current Equipment"
.Range("Q1").Value = "."
.Range("AD1").Value = "ID #"
.Range("AE1").Value = "LED Watt Rplc"
.Range("AF1").Value = "Install Date"
.Range("AG1").Value = "Contractor Notes"
.Range("AH1").Value = "CMP Action"
.Range("AI1").Value = "CMP Field"
.Range("AJ1").Value = "CMP Field"
.Range("AK1").Value = "CMP Field"
.Range("AL1").Value = "Locked Columns A:AC = UnLocked Columns AD:AK"
Worksheets("CWS").Cells.EntireColumn.AutoFit
' Worksheets("CWS").Cells("AD1:AK1").Select
' With Worksheets("CWS").Selection.Interior

' End With
With Worksheets("CWS").Range("AD1:AK1").Select
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
.Locked = True
.FormulaHidden = False

End With

 

[Joe4]

The properties that you have listed are under "Interior".

So I think that this line:

With Worksheets("CWS").Range("AD1:AK1").Select

needs to look like this:

With Worksheets("CWS").Range("AD1:AK1").Interior

 

[fvisions]

The properties that you have listed are under "Interior".

So I think that this line:

With Worksheets("CWS").Range("AD1:AK1").Select

needs to look like this:

With Worksheets("CWS").Range("AD1:AK1").Interior

That works to color the cells. Now how to I lock the same cells so that the text is not changed by the end user? Tried two different ways below but neither worked?

 With Worksheets("CWS").Range("AD1:AK1").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
        .Locked = True
        .FormulaHidden = False
  End With
' With Worksheets("CWS").Range("AD1:AK1")
 '   .Range("AD1:AK1").Select
  '  .Range("AD1:AK1").Locked = True
   ' .Range("AD1:AK1").FormulaHidden = False
'    End With

 

[Joe4]

That works to color the cells. Now how to I lock the same cells so that the text is not changed by the end user? Tried two different ways below but neither worked?

That code would look something like this:

Worksheets("CWS").Range("AD1:AK1").Locked = True

But note that locking by itself does not protect the range. You must also protect the sheet afterwards in order for the locking to take effect.