Disabling columns based on the answer in another cell

[EKKB]

Hi, I am new to excel programming etc

I have one column with 3 answer options. Colonoscopy, Endoscopy, Combined endoscopy and colonoscopy
If C is selected I want to block out columns relating to the other two options (further on in the worksheet).


Specifically if

Colonoscopy is selected in column D, I want to disable columns L-R (for that row)
If Endoscopy is selected in column D (for a particular cell) , I want to disable columns S-Y (for that row)
And if Combined is selected I want all columns available.

If I could lock them and black them out/grey them out so that its clear that a response is not required it would be great.

Is this possible?

Thank you

[alansidman]

Give this a try:

Please Login or Register  to view this content.

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

[ufopilot]

Not knowing how your sheet is set up, and if and where you can place any formulae I have just created a couple of scenarios.
There is one solution on sheet1, and another on sheet2.
Perhaps you can post a sample of your workbook for any further assistance.

(edited)
I just seen you have received an answer, but I can't seem to delete this reply.

[EKKB]

Hi

Thank you for your help. I did try the code above but cannot see that anything happened.
(Mind you , I have had to change the column letters due to a changed spreadsheet).

I have attached a copy of my spreadsheet as the other poster asked. Ideally, I want it so that if someone selects ''colonoscopy'' in column D then only those columns with the heading Colonoscopy are available to enter data in, the others are greyed out.

I really appreciate your help.

[alansidman]

See attached. Change the color code in the VBA as you desire using this page as a source of information

http://dmcritchie.mvps.org/excel/colors.htm

FYI: 15 is grey