Protect Conditional Formatting

Question

Hi All

I am using Office 2019 on windows 10

I have a worksheet that is protected (Locked) but the user input fields are not.

in the user input fields I have individual conditional formatting rules.

If a user dose a drag to auto enter fields the conditional formatting corrupts (it takes a long time to re-format) :(

is there a way to stop this happening?

Kind regards

Glen

[Moved from Excel/ Windows 10/ Office 365 for Business]

Answer 1

To:  Glen

Re:  VBA example

On a new Excel worksheet...

Right-click the sheet tab and select "View Code" from the popup menu.

In the large white window on the right, paste in the following code:

[Code has been edited]

'---

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo FixThings

 Application.EnableEvents = False

 If Target.Cells.CountLarge > 1 Then

    MsgBox "One cell at a time please"

     Application.EnableEvents = True

    Exit Sub

 Else

    If Target.Address = "$B$3" Or Target.Address = "$F$3" Or Target.Address = "$J$3" Then

       If Not IsNumeric(Target.Value) Then

          Application.Undo

          MsgBox "Numbers only"

          Application.EnableEvents = True

          Exit Sub

       ElseIf Target.Value > 100 Then

          Target.Interior.Color = vbRed

       ElseIf Target.Value > 50 Then

          Target.Interior.Color = vbYellow

       ElseIf Target.Value > 0 Then

           Target.Interior.Color = vbGreen

       Else

           Target.Interior.ColorIndex = xlColorIndexNone

       End If

    End If

 End If

FixThings:

Application.EnableEvents = True

End Sub

'---

Under the File menu - click "Close and return to Microsoft Excel"

On the worksheet enter something in B3 or F3 or J3.

Answer 2

To:  Glen

Re:  protect entry field

Well, you could turn off cell drop and drag on each users computer, but that is probably not practical.

Using Data Validation with a 'List' of acceptable entries is a possibility.

(but copy and paste overrides the list)

Or you could have VBA automatically format an input field when any change is made to it...

  Private Sub Worksheet_Change(ByVal Target As Range)

   'Target can be limited to the input fields.

  End Sub

'---

Free Excel add-ins and worksbooks at MediaFire...

http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents