Excel color retrive with drop down list

[Antrew80]

Hi i created an excel file with Cyprus League teams. I made a drop down list with these teams in a sheet named teams. i colored each team with its official color. i have a separate sheet named rounds. when i select two teams for example it does not come with the color as created.any idea of how to retrieve color also?


thanks a lot

 

[Roderick_E]

This depends where/how you're storing the color related to each team. For example, suppose you have a static list of team names somewhere (from whence the dropdown list is generated). These cells could contain the team color. Thus, when the user selects the team, a lookup checks for the team name in the static list and a VBA code uses the color from the static list cell. .cells(x,y).interior.color

 

[FDibbins]

Excel formulas/functions work on data, and color is not data - it is a formatting/cosmetic appearance applied so we can see things better.

The DD will show those names because they are data, it will not pull in the color of those cells because that is formatting/cosmetics, not data

 

[Antrew80]

This depends where/how you're storing the color related to each team. For example, suppose you have a static list of team names somewhere (from whence the dropdown list is generated). These cells could contain the team color. Thus, when the user selects the team, a lookup checks for the team name in the static list and a VBA code uses the color from the static list cell. .cells(x,y).interior.color

Hi Roderick,
thanks for your reply.
yes i have a sheet named teams where is the dropdown list and two different sheets for regular season and one for play off.
where i should put the code that mentioned above?

if you want i can upload the excel

thanks for your help.

 

[Roderick_E]

Hello, how are you producing the dropdown list? Are you referencing a range of cells or did you type the names in manually?

My plan was that you would use a range of cells, each cell with the team name is colored accordingly. Then you'd need a macro to match the dropdown selections to the list and utilize the cell color from the match.

 

[Antrew80]

Hi,
yes it is like you described above. each cell with the team name is colored accordingly.
i have to make one macro for each team or one macro for the drop down list?

thanks

 

[Roderick_E]

One macro. More than likely we'll use the Worksheet_Change macro.

Where do you want the colors to show? What sheet(s) and cells?

 

[Antrew80]

Sheet 1 (named FootBall) Column A (A1 - A240) and column D (D1 - D240)
Sheet 2 (named Play Off) Column A (A1 - A100) and column D (D1 - D100)