How to automatically colour cell background with value selected from custom dropdown list in MS Excel 2010

Question

In MS Excel 2010 Professional is there a way to automatically copy the background colour of the custom dropdown values from somewhere else in a spreadsheet?

I have some Data Validation on a row of cells that comes from two custom cells which have a background:

A53 and A54 look like this:

When selecting from the dropdown in my SS how can I configure it so that it turns green or red and not just populates with the text?

Answer 1

Data validation will not change formatting of the cells. You will need to use Conditional Formatting to do what you desire.

Once you have your lists in place, apply conditional formatting rules to those cells which use a formula that checks for the words RESTRICT and CASCADE.

1. Select the cells with the lists
2. Create a new conditional formatting rule
3. Choose Use formula to determine which cells to format
4. Add the formula =D1="CASCADE" (D1 will be the first cell in the group you selected)
5. Click the Format... button and choose the green fill
6. Click OK twice.

You will do the same for the RESTRICT text with red formatting. When you are done you will have two rules.

You will notice the Applies to window will show the cells the rules are applied to (in this example I used the entire D column; $D:$D). You do not need to create a formula for each row, the rule does it for you automatically.

Now when I make a selection from a list in column D, it automatically formats is according to the rules we created.