VBA - Lock the cell based on Conditional Formatting

Hi -


Currently i have wide sheet from Col A to AL, inside that table i put some conditional formatting.
let say in column H, the user need to fill yes or no. and if they put Yes, column I, J and L the cells will be marked as Red which indicates they need to fill with some answer.
and if they put No (in column H), column I, J and L the cells will be mark with Gray which indicates they no need to fill anything.


now what i want to achieve, if the user fill with "No", column I, J and L the cell will be grayed and cell will be locked. while if the user fill with "Yes", they need to fill in red


is that possible using macro?


thanks in advance

Conditional format changes can not be detected automatically.

The usual automated solution is to watch manually changed cells that trigger the conditional format.

Post a short example file to get the best help as there are several considerations here.

Quote:

---

Originally Posted by Kenneth Hobs

Conditional format changes can not be detected automatically.

The usual automated solution is to watch manually changed cells that trigger the conditional format.

Post a short example file to get the best help as there are several considerations here.

---

Hi,
i can't attach the example here and not able to create table here.


in Cell H6, If the user fill with "Yes", then the user can fill in cell I6, J6 & L6 (unlocked mode). But if the user fill with "No" then the cell I6, J6 & L6 will be locked, the user can't write anything.

Hope that can help.

Thanks

Hi reza_doang!
Please refer to the attached file.

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> [h6].Address Then Exit Sub
ActiveSheet.Unprotect "123"
Range([i6:j6], [l6]).Locked = Target = "No"
ActiveSheet.Protect "123"
End Sub

---