Keep color coded name in dropdown list

[John_Whin]

I've got a List with player names and whether they shoot right or left (NHL). The players names who shoot right are colored red. When I use the dropdown list I want the red color to remain with the name.

How do I accomplish this?

Thanks

 

[Peter_SSs]

When I use the dropdown list I want the red color to remain with the name.

I assume this means that once a name has been chosen from the drop-down then it will show the appropriate colour.

If so, try Conditional Formatting like this.
A1:A3 all have Data validation and Conditional Formatting as shown for A1

Excel Workbook
	A	B	C	D	E
1	Tony			Player	LR
2	Todd			Tom	L
3	Tim			Ted	R
4				Tim	R
5				Todd	L
6				Tony	R
Match Colour

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =VLOOKUP(A1,$D$2:$E$6,2,0)="R"	Abc
D2	1. / Formula is =E2="R"	Abc

#VALUE!

 

[Yongle]

Or subtly alter the names so that Excel can distinguish RIGHT shooters
- a trailing space is invisible to the viewer

Below I added a space AFTER the name of each RIGHT shooter "Peter " "Janet " but did not for LEFT shooters "David" "Henry"

Determine conditional formatting in D2 using this formula =RIGHT(D2,1)=" "
( same method could be applied to source list A2:A5 )

when Peter is selected in D2

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Players[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]David[/td][td][/td][td][/td][td]Peter [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Peter [/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Henry[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Janet [/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: List[/td][/tr][/table]

when Henry is selected in D2

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Players[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]David[/td][td][/td][td][/td][td]Henry[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Peter [/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Henry[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Janet [/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: List[/td][/tr][/table]

 

[John_Whin]

Thanks, Yongle. That's what I'll do.

 

[John_Whin]

Strangely under Font tab when I try to conditional format a color it always goes back to 'Automatic'. I can format 'Bold' and it does that. The only available option under Font is Font style; Color, Size etc is greyed out. I can go to Fill and Background Color the whole cell properly. Color does not work for Border options either. I really would prefer to just color the text.

Thanks

 

[Peter_SSs]

Strangely under Font tab when I try to conditional format a color it always goes back to 'Automatic'. I can format 'Bold' and it does that. The only available option under Font is Font style; Color, Size etc is greyed out. I can go to Fill and Background Color the whole cell properly. Color does not work for Border options either. I really would prefer to just color the text.

Thanks

It is possible that your worksheet has become corrupted. First thing I would suggest is to start a new workbook and set up a tiny sample like in this thread and see if it works there.

 

[John_Whin]

the test worked on another sheet in the workbook, then I tried on the initial sheet. For some reason it worked fine that time.
Thanks Peter_SSs

 

[Peter_SSs]

the test worked on another sheet in the workbook, then I tried on the initial sheet. For some reason it worked fine that time.
Thanks Peter_SSs

You're welcome. Glad it all resolved in the end.