How to use Conditional Formatting on a series of Blank Cells based on the sole answer from a different cell


How to use Conditional Formatting on a series of Blank Cells based solely on an answer from a different cell. I'm currently working on an Excel Survey that can be found on my web site, this survey will consist of over 50 questions; almost 40% of those questions relate solely on a trigger answer from a question that is asked in the beginning of the survey. Example, first survey question asks if the applicant is an American Citizen, if he/she answers "Yes", than I want approximately 40% of a specific set of cells to automatically change into a certain color background filled, this way the applicant does not need to waste his/her time trying to answer the rest of the survey. We want the applicant to concentrate only on those cells that have changed colors due to the trigger answer/question, just a f.y.i, the specific set of cells that we want color filled have already been formatted with data validation drop down box with multiple choices, didn't know if this affects it in anyway.

Thank you for any recommendation, Windycity




Hello windycity,

If you are still looking for a solution you could use this:

If the cells you want to colour are contiguous:

When adding the formatting rule, select the last option - "Use a formula" - and enter this in the formula field: =$A$1="y" (edit $A$1 to the cell where the citizenship answer will be, and edit 'y' to the answer style in your survey) and then in the "Applies to" field enter the range you want to colour; ex: =$A$2:$A$10

If the cells you want to colour are not contiguous:

Use the same formula as above. In the "Applies to" field enter the cells you want to colour; ex: =$B$2,$B$4,$B$6,$B$8,$B$10 (you can also mix single cells and ranges - =$B$2,$B$4:$B$6,$B$8,$B$10,$B$16:$B$20,$B$25)

Hope this helps (allbeit a tad late)

Cheers   ;-}


Answer the Question

You must create an account to use the forum. Create an Account or Login