Keep color coded name in dropdown list

John_Wh

Board Regular
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

MrExcel MVP, Moderator
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
ABCDE
1TonyPlayerLR
2ToddTomL
3TimTedR
4TimR
5ToddL
6TonyR
Match Colour
Conditional Formatting
CellConditionFormat
A11. / Formula is =VLOOKUP(A1,$D$2:$E$6,2,0)="R"Abc
D21. / Formula is =E2="R"Abc
Data Validation
A1List
=$D$2:$D$6
 

Yongle

Well-known Member
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
A
B
C
D
1
Players
2
DavidPeter
3
Peter
4
Henry
5
Janet
Sheet: List

when Henry is selected in D2

Excel 2016 (Windows) 32 bit
A
B
C
D
1
Players
2
DavidHenry
3
Peter
4
Henry
5
Janet
Sheet: List
 

John_Wh

Board Regular
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
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
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_Wh

Board Regular
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
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top