Excel 2007: keep cell style as definied from data validation sheet cells

Question

in Excel, how do you do to keep cell style in a column with data validation list from another sheet?

I have a sheet with a list of choices, each choice being a cell with it's own style (different background color for each). In the sheet where user input values, how can the cell gets the same style as the option being chosen from the validation data?

Example: list name "myList"

• sheet "Lists" have a "myList" cell range of 3, each with a value and a style background color. ("item 1" has red background, item 2 has yellow background, etc.)
• sheet "Data" have a column with data validation of "myList", when I select one of the option, the cell keep it's default style.

I want the cell in "Data" sheet to have the same style as the cell from "myList" when selecting a choice in the data validation list.

Thanks

Answer 1

As far as i know, there is no automated way to have the data validation works as you wish, meaningly, copy the format of the list source.

You can either :

• create conditional formating conditions depending on the values of your list (but you'll have to update them in the conditional format menu)
• create vba code using the event Worksheet_Change and compare the value of the Target to the value of your Range("myList") and then copy the format