How to lock a cell based on a choice in a dropdown box?

[g0dmenuelz]

Hello, I need help Vba masters.

This might need a vba, can anyone help me if this is possible?

I need to lock all other cell based on the salary grade level. I managed to highlight it based on manually putting conditional formatting with a color red on each box.

But is there anyway to have a vba code for this so instead of highlighting the cell it will be disabled or lock or uneditable instead?

I just need the formula or how it is done and I can study and hopefully apply the rest.

I'd really appreciate any help.

Thanks.

[6StringJazzer]

I have analyzed your existing conditional formatting rules and cannot figure out the logic you want to apply. Also, you say "I need to lock all other cell" and "instead of highlighting the cell it will be disabled or lock or uneditable instead". What cells do you want to lock?

I suspect this can be done with data validation rather than VBA but it is unclear what you need to do. Please explain how you are using the DIVISION1 sheet and what you are trying to do.

[g0dmenuelz]

Hello,

Let's say an employee chooses, Grade A on the Sheet1!D4 cell. Since Grade A is for Basic, all cells that is not applicable for him (Intermediate, advanced and superior) on his column in Division A and Division B will be locked or uneditable.

Then another employee again will enter his name and choose a Salary Grade. If he selects intermediate then the advanced and superior is not applicable to him this applies to both column D in division 1 and Divsion 2.

I tried using conditional formatting which instead of locking a cell, it will highlight red instead.

But is there a way to make a cell not editable or disabled, locked etc based on the dropdown box? like a validation formula? Or does it need some vba?

Thanks for the help.

[6StringJazzer]

Your formatting works against you. I've done my best with your merged cells but merging cells is not recommended. People like to do it for presentation reasons but it makes data manipulation harder.

This assumes that the order of the names in row 2 of DIVISION1 is the same as in column B of Sheet1.

In DIVISION1!C5 use this as a Data Validation Custom rule:

=OFFSET(Sheet1!$D$4,COLUMNS($C1:C1)-1,0)="GRADED"

Repeat for the three BASIC ratings. Use GRADEC for INTERMEDIATE, and so on.

Once you have set these in the first column you can copy and Paste Validation to the subsequent columns.

I did C5 for you as an example.