Creating Conditioning Formatting based on IF Formulas

[ExcelDummy77]

Please see attached spreadsheet:
I want cells B14, B17, B20, B23, B26, B29 to turn colors (dark gray) if cell N5 states "discharge disposition." Cell N5 is based on answers from D5. The answers for the data validation drop box is below the questions. Those answers are based on algorithms I must follow.

I don't want the user to answer questions 2-7 if they are not needed based on the answers of N5.

Thanks for your help!!

[mehmetcik]

Try this workbook, I added a conditional format for you:

[TMS]

Select cells:

Please Login or Register  to view this content.

And use the CF Formula:

Please Login or Register  to view this content.

Then pick your colour.


Regards, TMS

[ExcelDummy77]

Looks great mehmetcik! Would you mind sharing how you did that so I could apply to 10 other worksheets I need to complete? Thanks for your help!

[mehmetcik]

TMS has given you instructions on how to do this.

So I thought I'd go one further create a macro to do it for you.

Please Login or Register  to view this content.

[TMS]

Thanks for the Rep

Note that with the macro provided, it is important to Select the "base" cell B3. While we normally advocate avoiding selecting pretty much anything, in this case it is essential, as Conditional Formatting is applied relative to the base cell. At least, that has been my experience and I would code this routine as mehmetcik has done.

Regards, TMS