Simple Bar Graph - attaching bar colours to specific data

I have a simple bar graph that displays the number of points a set group of people have attained in different competitions, eg, John 10, Lisa, 12, Paul 16, etc; with each person having their bar coloured specific to them, so John's bar is red, Lisa's always blue etc,. I realised once done that I didn't like the haphazard look of the bars, thinking it would be much better to sort the data so the bars were descending in order from left to right.

I've now done this and it does indeed look much better (and the order of the bars also reflects the 1st, 2nd, 3rd place score) but the problem is I cannot figure out how to ensure the colour of each bar remains specific to the individual. The bars all display in descending order but the bar colour doesn't stay with the relevant person, so for example, when John is 1st his bar colour is red, but when he's 3rd it is blue.

So I'm either stuck with having specific colours but the data spread without order across the chart, or displaying them in descending order but without specific bar colours. Is there any way I can fix this? Preferably using formulas as my VBA experience is near zero, but any help would be much appreciated.

I'm using Excel 2010

Thanks

Sure.

Here is the test version of the work book. On the left side of the top frozen pane you will see the bar chart. On the lower non frozen panes you will see rows of data showing the different people and their position in each competition. If you change the position order (via the drop down menu on each name) you will see the chart update to reflect the changes. (The code that handles all this is shown in green on the right hand side of the sheet)

In this sheet all the bars are now ordered from highest to lowest score, left to right. The problem is that if you change enough values to alter the order of the names on the chart, the bar colours do not change to match. There's no code to do this because I don't know how to even start. eg chart position 1 is always red, 2 is always yellow, etc regardless of who the person is in that position.

Opps sorry I meant to say "on the right side of the top frozen pane" not the left side.

Hi Andy

Thanks very much. However it doesnt seem to work. I changed a few values and the bar colours remain fixed and do not move with the change of person/position. However, you have given me some ideas. What was your thought process here, are you using multiple bars overlapped and only activating one to get the desired result?

Hi Andy

My apologies. I was being an idiot and looking at the wrong graph! This is perfect, thanks so much for your help.