Change cell color in Excel according to adjacent dropdown value

excel conditional-formatting
Drew picture Drew · Apr 14, 2010 · Viewed 51.6k times · Source

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

Adriaan Stander picture Adriaan Stander · Apr 14, 2010

In Excel 2007, 2010 or 2013, try

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

Select Conditional Formatting -> New Rule...

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

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.

Change color format

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

Format from B1 to B?

Related questions

Conditional Formatting (IF not empty)

How do I conditionally format a cell so if not blank it is grey? I tried to do 'not equal', but it didn't work. I am using Windows Office 2003 with Windows XP at work. I don't see the same feature …

Read More
Change the color of cells in one column when they don't match cells in another column

I want to check if the values in one column are the same as values in another column. Whenever the values are not the same, I would like to change the color of these cells. For example: Column I, Column …

Read More
Conditional Formatting using Excel VBA code

I have Range object called DistinationRange which contains reference to range B3:H63 I want to apply the following two conditional formatting rules using Excel VBA code dynamically. (Because the range would not be same all the time) If Cell …

Read More