How to Lock Cell based on the value of the another cell and perform data validation

Question

I am very new to VBA code. Now I was assigned to make a task that validate the data in an excel sheets.

For Example in my Column A, I have a drop down menu that enable the user to make a selection between "Yes" or "No" only.

If user selected "Yes", cells in Column B and Column C will be marked as Mandatory and cannot be blank. I want to put a validation error on this.
```
**Example 1: If A1 and A30 == YES**
```
- B1 and C1, B30 and C30 are mandatory
- Will fill a color to the mandatory cells and remove the fill color when the cells have value already
- Throw a validation error when these cells are blank and if exceeds the number of characters required.
  
  Example 2: If A99 == NO
- B99 will be locked and the user will not be allowed to enter a data on this cell. Possible that we can add a cell value as "NA" to avoid confusion

I was able to capture this using the data validation and conditional formatting. However, I cannot perform the locked functionality since as per research, I need a VBA code for this one.

I think you'll be able to do it via logic in the validation... — Nathan_Sav, Jul 01 '16 at 10:38
You might find this [question helpful](http://stackoverflow.com/questions/3037400/how-to-lock-the-data-in-a-cell-in-excel-using-vba). TL;DR: `ActiveSheet.Protect` will lock a worksheet. `Range("A1:D5").Locked = False` sets expcetions to the lock. Finally `ActiveSheet.Unprotect` restores read/write access. — David Rushton, Jul 01 '16 at 11:15

Harley B · Accepted Answer · 2016-07-01T15:07:36.070

Something like this should work.

Put it into the code module for the sheet you want to apply it to.

Private Sub worksheet_change(ByVal target As Range)

    ''''' CHECK IF THE CHANGED CELL IS IN RANGE A1:A99 (OR ANY OTHER RANGE YOU DEFINE)
    If Not Intersect(target, Range("A1:A99")) Is Nothing Then

        ''''' UNPROTECT THE SHEET
        ActiveSheet.Unprotect

        ''''' IF THE CELL CHANGED IS NOW 'YES'
        If target = "Yes" Then

            ''''' WE DEFINE HOW MANY COLUMNS TO MOVE ACROSS FROM THE CELL THAT CHANGED AND DO THE ACTIONS IN THE CODE BELOW
            ''''' SO IN THIS EXAMPLE WE'RE MOVING ACROSS 1 CELL TO B1 AND THEN 2 CELLS TO C1
            ''''' SO TO GET TO AA1 AND AB2 WE'D DO i = 26 to 27
            ''''' IF WE WANTED TO ACCESS AA1 ALL THE WAY THROUGH TO AZ1 WE'D DO i = 26 to 51
            For i = 1 To 2

                ''''' MOVE ACROSS i NUMBER OF CELLS FROM THE CELL THAT CHANGED
                With target.Offset(0, i)

                    ''''' UNLOCK THE CELL
                    .Locked = False

                    '''''SET THE CONDITIONAL FORMATTING
                    .FormatConditions.Add Type:=xlExpression, Formula1:="=ISBLANK(" & target.Offset(0, i).Address & ")"
                    With .FormatConditions(.FormatConditions.Count)
                        .SetFirstPriority
                        .Interior.ColorIndex = 37
                    End With

                End With

            ''''' INCREASE i BY 1 AND LOOP TO AFFECT THE NEXT CELL
            Next i

        ''''' IF THE CELL CHANGED IS NOW 'NO'
        ElseIf target = "No" Then

            ''''' WE DEFINE HOW MANY COLUMNS TO MOVE ACROSS FROM THE CELL THAT CHANGED AND DO THE ACTIONS IN THE CODE BELOW
            For i = 1 To 2

                ''''' MOVE ACROSS i NUMBER OF CELLS FROM THE CELL THAT CHANGED
                With target.Offset(0, i)

                    ''''' SET THE CELL VALUE TO BLANK
                    .Value = ""

                    ''''' LOCK THE CELL
                    .Locked = True

                    ''''' REMOVE THE CONDITIONAL FORMATTING
                    .FormatConditions.Delete

                        ''''' ADD NEW CONDITIONAL FORMATTING HERE IF REQUIRED

                End With

            ''''' INCREASE i BY 1 AND LOOP TO AFFECT THE NEXT CELL
            Next i

        End If

        '''''PROTECT THE SHEET
        ActiveSheet.Protect

    End If

End Sub

Be sure to set locked to false in your A column where the drop down lists are or users won't be able to change the drop down value while the sheet is locked.

The code worked but seems to have an issue. At first it is working but when I tried another instance in A2, it will not work and will throw an error that this cell is also locked. — PeterS, Jul 01 '16 at 14:32
See my note at the end of my answer. Your A column is locked by default. Unprotect the sheet (in 'Review' tab 'Unprotect Sheet') then highlight all the cells where your drop down lists exist in column A and right click, format cells, then click the 'Protection' tab and uncheck 'Locked' — Harley B, Jul 01 '16 at 14:35
Thanks! :), but what if I need to specify another cell? For example I hit "Yes" then I want another cell like AA1, AB2 to be mandatory and cell K1 and P1 to be optional only with another color as indicator for optional? — PeterS, Jul 01 '16 at 14:49
Updated my answer with extensive comments to explain each step. I'd encourage you to try to understand the code so you can manipulate it to your exact requirements. — Harley B, Jul 01 '16 at 15:03

How to Lock Cell based on the value of the another cell and perform data validation

1 Answers1