The image below provides a simple illustration of what I think you want to achieve.
Here, the entire row of the table is green when Yes is entered and pink when No is entered. Otherwise, the row is white.
As I'll explain, you can use conditional formats to do what you want. But first, let me make a suggestion...
It's never a good idea to ask users to enter "yes" or "no" in a cell. Not only does this take them more time than is necessary, it significantly increases the chance of errors.
Errors are important here because your conditional formatting only will work if you can rely on accurate entries in the Yes-No column.
So, instead of entering text, I would ask users to enter 0 (zero) for "no" and 1 (one) for "yes". If you want your spreadsheet to display the answer differently, you can do so with number formatting.
To assign a number format that would display "Yes" for 1 and "No" for 0, select the column where you want Yes or No to be displayed. Press Ctrl+1 to display the Format Cells dialog.
Or, to use standard commands:
...In Excel 2003, choose Format, Cells.
...In Excel 2007 and above, choose Home, Cells, Format, Format Cells.
In the Number tab, choose the Custom category. Then enter this custom number format in the Type edit box:
Excel number formats use this syntax:
[positive numbers];[negative numbers];[zero];[text]
So the yes-no format above displays "Yes" for a positive number, nothing for negative numbers, "No" for zero, and nothing for text. (By displaying nothing for incorrect entries, we provide automatic error-checking.)
To set up the conditional formats for this table, you need to specify formulas rather than cell values.
Select all cells in your table. Make a mental note of where your
active cell is within this selection. Then...
In Excel 2003...
Choose Format, Conditional Formatting, and then set up your dialog to look something like this:
There are two key points here. First, be sure to chose "Formula Is" in each dropdown list box at the left of this dialog.
Second, when you enter the formula, be sure to enter it using a mixed reference to a cell in the row of the active cell. For example, the formula shown for the first condition is:
When I set this up, my active cell was in row 4, so my formula referred to row 4. But notice that there's no $ sign in front of "4". That's because I want the formula to change to row 5, 6, and so on when the conditional formatting is used in those rows. On the other hand, there is a $ in front of "C" because I want the formula always to refer to the value in that column.
To add each new condition, choose Add >>. After three conditions are defined, "Add >>" is grayed out because Excel 2003 allows a total of three conditional formats.
After you enter all three formats, choose OK.
In Excel 2007...
This figure shows the results we want. Here' I've used light orange for No and light green for Yes.
To set conditional formatting, choose the range B4:C7. And note which row your active cell is in.
Choose Home, Styles, Conditional Formatting, Manage Rules. Choose New Rule.
In the New Formatting Rule dialog, choose "Use a formula to determine which cells to format." In the edit box, enter this formula:
We don't need to specify any formats for this condition. It's purpose is to keep Excel from treating blank cells and cell with zeros in them as the same thing. So just choose OK.
(Again, when I created these formulas my active cell was in row 4. If your active cell is in a different row, you'll need to modify your formulas as needed.)
Repeat the process of setting up a new rule. Enter this formula:
This time, specify the formats you want for the No response. Then choose OK.
Repeat the process for Yes, entering this formula:
Specify the format for Yes.
After you choose OK, compare your Rules Manager dialog to this example:
You should see two critical differences between your dialog and this one.
First, the first formula you entered is at the bottom. This is a problem because Excel needs to process this rule first. The order of the other two rules doesn't matter.
To correct this problem, we need to move the first rule we created to the top of the list. To do so, select the rule in this dialog and click twice on the blue up-arrow.
Second, your dialog probably doesn't show the Stop If True checkboxes checked. You need to add checks to them all. That way, when Excel finds one condition that works it doesn't continue to process the other rules, messing up our results.
Choose OK to complete your conditional formatting with formulas.
This approach would work in either version of Excel.
All the best,