How can I make custom data label colors conditional by text value?

[Skrimgeour]

I am working to create an XY Graph that shows XY value, but also has different data types called out by region(i.e. Values for each State). I have created conditional formatting to create custom data labels.


My challenge is there is a drop down menu where the state the value comes from is highlighted (i.e. Mass, RI, Conn, NH, VT, etc.,) And I would like the label to automatically change color based on the state selected in a drop down menu. (i.e. Mass data is blue, NH is red).


Right now I can do it manually, but I am wondering if there is a way to automate it? The custom values relate to numbers, not text, so I couldn't make that work. Additionally, the data label comes from column A, but the drop down is column D.


Help! I use Excel 2010.

 

[Hui]

Do you have a range of cells with a list of the States and Colors you want?

 

[Skrimgeour]

Yes. Right now I have 50 cells plotted. I may expand to 100, but for now I have a range of 50 cells.

 

[Hui]

Do you have a list of the states and the colors you want?

What range eg A1:B50 are they in ?

 

[Skrimgeour]

Thanks so much for your help.


To be clear, I can change the text color using conditional formatting in the A column, but I can't get it to reflect on the chart.


Basically: A2:A52


Colors:

Massachusetts - Red

Conn - Blue

VT - Green

NH (black, so leaving it since I believe there can only be the three custom colors in Excel data labels)

 

[keymaster]

Simple solution is to split your data in to 4 series and color the labels differently. The cell formatting (conditional or not) doesnot reflect in chart labels.


An alternative could be to use macros...