-1

I have done a simple condition formatting =$B7="PP" applies to =$F$7:$M$1000

This will Turn dark grey if "PP" is selected.

I now want to lock cells F7:M1000 if "PP" is selected from a drop down list anywhere in B7:B1000. I do not want anyone to be able to type in F7:M1000. I am a complete beginner on VBA so if it can be done via VBA then easy instructions will help me to do this.

Community
  • 1
  • 1
Niffy
  • 15
  • 1
  • 7
  • Did you search Google? Did you try to record a macro? – Siddharth Rout Jan 07 '14 at 11:19
  • [**this**](http://stackoverflow.com/questions/16684297/hiding-formulas-in-formula-bar/16686868#16686868) partially covers an answer to your question –  Jan 07 '14 at 11:20

1 Answers1

4

First, unlock cells B7:B1000. You want the user to always be able to edit these.

Then protect the sheet. This will make sure that locked cells cannot be edited. (If the sheet isn't protected then having cells locked or not locked makes no difference.)

Then add this in the Sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ErrorHandler
    Application.EnableEvents = False 'prevent infinite event loops

    Me.Unprotect ' else won't be able to modify locked cells

    If Not Intersect(Target, Range("B7:B1000")) Is Nothing Then
        'User edited a cell in this range.
        With Range("F1:M1").Offset(Target.Row - 1, 0)
            If Target.Value = "PP" Then
                .Interior.Color = RGB(200, 200, 200) 'gray
                .Locked = True
            Else
                .Interior.Color = RGB(255, 255, 255) 'white
                .Locked = False
            End If
        End With
    End If

ExitProcedure:
    Me.Protect
    Application.EnableEvents = True
    Exit Sub
ErrorHandler:
    MsgBox Err.Description 
    Resume ExitProcedure

End Sub
Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
  • That did not work. Its highlighting all Cells from B7:M1000 in grey regardless of whether i have selected PP or not. Also because the spreadsheet is protected I cannot even select PP from the drop down because it tells me its protected. In B7:B1000 i have a data validation set up to be able to use a drop down. You can only select PP or SW but i want cells F7:M1000 to lock when PP is selected and only that row to turn grey. example: if B7 is selected with PP then i need Cells F7:M7 to lock and turn Grey. Same method throughout the whole sheet. If B7 is blank or has SW then do nothing. – Niffy Jan 07 '14 at 12:14
  • 2
    Okay, so this answer doesn't do exactly what you want. What do you do next. Have you tried modifying it to suit your exact needs? Because this answer does point you in the right direction. – Jean-François Corbett Jan 07 '14 at 12:57
  • I am a complete beginner to VBA, just reading the code is confusing. It is still locking the whole sheet also columns A to E should always be unlocked but they are also locking. – Niffy Jan 07 '14 at 13:33
  • I want a code that will just lock F7:M1000 if B7:B1000 is PP. This does not answer the question. the response above is locking the whole sheet which is not what i wanted. – Niffy Jan 07 '14 at 13:36
  • ITS WORKING! I have been at this for weeks. I unlocked cells A to E then applied your code. Its now doing its job. Your answer was smack on. Thank you – Niffy Jan 07 '14 at 13:58
  • + 1 For nice followup! – Siddharth Rout Jan 07 '14 at 14:33