EXCEL 2010 - Locking Cells based on Cell Value

Question

Here is a basic set-up that I am trying to acheive:

Column A has a validation list for "Yes" or "No" only.

Column B will have data entered ONLY IF Column A has a value of "No".

Is there a way to prevent data entry to a cell in Column B if the neighbor cell in Column A has a value of "Yes"?

This would be the preferred method.

A second solution would be to Conditionally Format Column B with a data entry based on the Column A value of "Yes".

A third solution would be to Conditionally Format Column B with a Color fill based on the Column A value of "Yes".

Answer 1

Well, this worked... but only for my worksheet.  Since this is a shared doc, it did not work for any of the others in the worksheet as well.

Answer 2

Hi,

The correct changes for Col F & G are this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If Not Intersect(Target, Columns(7)) Is Nothing Then

 If UCase(Cells(Target.Row, "F")) = "YES" Then

 Application.EnableEvents = False

 MsgBox "Range G" & Target.Row & " is locked"

 Cells(Target.Row, "H").Select

 Application.EnableEvents = True

 End If

 End If

 End Sub

Answer 3

So, the columns that I am actually needing this are F & G as opposed to A & B.

I copied and pasted the code and changed, what I believe to be, A & B accordingly as below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Columns(2)) Is Nothing Then

If UCase(Cells(Target.Row, "F")) = "YES" Then

Application.EnableEvents = False

MsgBox "Range G" & Target.Row & " is locked"

Cells(Target.Row, "H").Select

Application.EnableEvents = True

End If

End If

End Sub

The error I receive is "Compile Error: End If without block If"

Thoughts?

Also, can macro enabled worksheets be shared worksheet?

Answer 4

Hi,

Right click your sheet tab, view code and paste this code in on the right. Close VB editor and try selecting any cell in Col B where there is a Yes in the same row of Col A.

You will need to save your workbook as macro enabled with a .xlsm extenstion

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 If Not Intersect(Target, Columns(2)) Is Nothing Then

 If UCase(Cells(Target.Row, "A")) = "YES" Then

 Application.EnableEvents = False

 MsgBox "Range B" & Target.Row & " is locked"

 Cells(Target.Row, "C").Select

 Application.EnableEvents = True

 End If

 End If

 End Sub