Lock Drop down Menu

[Mubarak]

Hello!
I made a drop down menu(Data>Validation>...), I want that menu to be locked
after a specific value is chosen from the list,
There is 3 options :
1-Open
2-Closed
3-On Hold

I want the cell to be locked after "closed" is choosen, and one more thing I
want colors !

 

[Jacob Skaria]

Hi Mubarak

1----By locking if you mean not to allow the user to change the value then
try the below macro. Right click the sheet tab, View code and paste the code.
I am assuming you have the drop down in Column D

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("D")) Is Nothing Then
If Target.Text = "Closed" Then Target.Offset(, 1).Select
End If
Application.EnableEvents = True
End Sub

2----For coloring the status use Conditional Formatting. Selec1. Select the
column/Range. From menu Format>Conditional Formatting>
For Condition1>Select 'Cell value' select 'equal to' enter 'Closed'. Click
Format Button>Pattern and select your color. You can add condition2 and
condition 3 for the other status if you need.. and once done Hit OK.


If this post helps click Yes

 

[Mubarak]

Dear Jacob,

In case I want to re-open the locked ticket whats the needed code

Thanks in Advance