Change cell color in Excel according to adjacent dropdown value

Question

I understand how to make a dropdown list. I understand how to make conditional formatting change the color of a cell. What I do not understand is how to make conditional formatting change the color of a cell based solely on the state of another cell (not a comparison).

A1 is a No / Yes dropdown list

B1 is a criteria statement

If the user satisfies the criteria statement in B1, they select Yes on the dropdown list in A1. For quick reference, if possible, I'd like B1 to change to a green background color on this event, and of course change back to no-fill if the dropdown is reset to No.

Is this possible in Excel 2003 and/or 2007?

Answer 1

In Excel 2007, 2010 or 2013, try

• Under the home tab select Conditional Formatting -> New Rule

• Use the Use a formula to determine which cells to format

• For Cell B1 the formula can be "=(A1="Yes")", and change the formatting back color to what you wish.

• You can then extend the formatting from B1 to B? as required.