changing cell formats

[dr01allen]

I'm looking for a way to change the format of a cell to match that of another cell.

For instance when I enter the work 'david' in A2, I want it to have the same background as that in a list from A30:A40.

Application:

I have a list of names in cells A30:A40. Each name has a different format (text and background color).
The sheet as a range of dates along the top and a list of activities along the side as well as a list of names at the bottom the task list.
When someone wants to sign up for a task and date, they type their name in the appropriate cell and the correct format is applied to that cell.
I could put in a bunch of conditional format rules but that means when someone adds or changes the list of names, they have to update the rules.

Any suggestions?

 

[JumpingCrab]

You can define conditional formats that apply to the table, where you have each condition look at the value of one of the names in the list at the bottom.
That way, when you change the names in the list, the conditional format now applies for the new name.
If you add names beyond the 11 you have in your list, you'd have to add another conditional format, but at least you don't have to change the formats for all cells in your calendar.

 

[dr01allen]

You can define conditional formats that apply to the table, where you have each condition look at the value of one of the names in the list at the bottom.
That way, when you change the names in the list, the conditional format now applies for the new name.
If you add names beyond the 11 you have in your list, you'd have to add another conditional format, but at least you don't have to change the formats for all cells in your calendar.

Any suggestions on how to do that?

 

[JumpingCrab]

You would use a separate conditional format for each name, which applies to the full calendar as well as the name list.
So, select the calendar and the name list, then Conditional Formatting>Highlight Cell Rules>Equal to...
Select the first cell in the name range as the value for the condition, making sure to have absolute references ($A$30).
Set the format you want for the cell, et voila!
Repeat for all names in the list, each time selecting the next name ($A$31, $A$32, etc...) for the condition.
So you end up with 11 conditional formats that all apply to the same area (the calendar and the name list), but each with a different condition.

This way you can change the name in the list and have a new name used to highlight cells in the calendar, without having to redo all the conditional formatting.

I hope this is clearer than mud
I can't post a picture to show it.

JL