VBA Worksheet Protection- Conditional Formatting

[Donai]

Hi, how can i add a condition to allow conditional formatting of cells as True but normal formatting as False

, AllowFormattingCells:=False _

Public Sub protectSheet(ByRef wks As Worksheet, ByVal strPW_Wks As String)

    
    wks.Protect _
        Password:=strPW_Wks _
        , DrawingObjects:=True _
        , Contents:=True _
        , Scenarios:=True _
        , UserInterfaceOnly:=True _
        , AllowFormattingCells:=True _
        , AllowFormattingColumns:=True _
        , AllowFormattingRows:=True _
        , AllowInsertingColumns:=False _
        , AllowInsertingRows:=False _
        , AllowInsertingHyperlinks:=False _
        , AllowDeletingColumns:=False _
        , AllowDeletingRows:=False _
        , AllowSorting:=True _
        , AllowFiltering:=True _
        , AllowUsingPivotTables:=False
    
End Sub

 

[bjurney]

Your conditional formatting would still work as long as you set it up before you protect the worksheet if you set the

AllowFormattingCells:=False

its just that the user wouldnt be able to edit the conditional formatting that was already set up.

Not sure if there is a way to have the user edit the conditional formatting if AllowFormattingCells:=False though.

 

[Donai]

I have the below code in sheet1, i then protect my sheet, i have unlocked the cells, i have set the formatting cells to False, when i type 2 in cell A1 i get run time error 1004 application defined Error.

Why is this?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim Rng1 As Range

    On Error Resume Next
    Set Rng1 = Range("A1:W5000").SpecialCells(xlCellTypeFormulas, 1)
    On Error GoTo 0
    If Rng1 Is Nothing Then
        Set Rng1 = Range(Target.Address)
        Else
        Set Rng1 = Union(Range(Target.Address), Rng1)
    End If
     
    For Each Cell In Rng1
        Select Case Cell.Value
           Case vbNullString
                Cell.Interior.ColorIndex = xlNone
                Cell.Font.Bold = False
            Case 1
                Cell.Interior.ColorIndex = 46
                Cell.Font.ColorIndex = 2
                Cell.Font.Bold = True
            Case 2
                Cell.Interior.ColorIndex = 36
                Cell.Font.ColorIndex = 0
                Cell.Font.Bold = True
            Case 3
                Cell.Interior.ColorIndex = 7
                Cell.Font.Bold = True
            Case 4
                Cell.Interior.ColorIndex = 41
                Cell.Font.ColorIndex = 2
                Cell.Font.Bold = True
            Case 5
                Cell.Interior.ColorIndex = 3
                Cell.Font.ColorIndex = 2
                Cell.Font.Bold = True
            Case Else
                Cell.Interior.ColorIndex = xlNone
                Cell.Font.Bold = False
        End Select
    Next
End Sub