Gray out and deactivate a particular cell when another cell equals 1

[gregglazar]

I know this is possible, but I need a little help achieving it.

I need to gray out and deactivate cells F16:F19 so no text can be
inputted into these cells when cell L14 = 1. When cell L14 is not
equaol to 1, I would like cells F16:F19 to function as normal. Can any
one help me with this? I'm not too fussed how this is achieved -
whether it is through a macro, coding etc.

Your help will be much appreciated.

Cheers

 

[Franz Verga]

Nel post *[email protected]* ha scritto:

I know this is possible, but I need a little help achieving it.

I need to gray out and deactivate cells F16:F19 so no text can be
inputted into these cells when cell L14 = 1. When cell L14 is not
equaol to 1, I would like cells F16:F19 to function as normal. Can any
one help me with this? I'm not too fussed how this is achieved -
whether it is through a macro, coding etc.

Your help will be much appreciated.

Cheers

Hi,

You can achive with a combination of Data Validation and Conditional
Formatting.

1) Data Validation
Select your range F16:F19 and then from menu Data, Validation choose Custom
from the drop-down and type this formula in the text box: =$L$14<>1

2) Conditional Formatting
Select your range F16:F19 and then from menu Format, Conditional Formatting
choose Formula is under Condition 1 and type this formula: =$L$14=1 then
choose the color you want for Font and for Patterns when L14 = 1

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[gregglazar]

Appreciated, but not exactly what I had in mind. This does a sufficient
job, but not a great job. I was hoping to actually deactivate the cells
entirely so they cannot be selected, and as such text cannot be
entered, rather than just changing the colour of the cells and
resrtricting input and displaying a dialog box. It will require VB
coding, something I am terrible with.

Is this something any one out there can help me with?

 

[NickHK]

You would have to toggle the Locked property of those cell on a protected
worksheet.
Record a macro of protecting the WS (Tools>Protection>Protect sheet) and
Locking/Unlocking the cells (Format>Cells>protection>Locked).
Also check the help on how the use .Protect with the "UserInterfaceOnly"
argument.

NickHK