Automatic coloring pivot chart bars

[i-fathy]

Hi
I have a sheet in which records for different players in different teams are added

4 teams
And multiple players.. around 40 players total

When i make the pivot chart to show the records for all players, im trying to give each player bar a color depending on player's team name
Is there a way to do that automatically?

For example
Blue teamp player: blue bar
Red team player: red bar
Orange team player: orange bar
Etc

The thing is that the data sorting is changed all the time from top to bottom record
And if i color bars manually, the color remain on bars regardless of player name on that bar

[noboffinme]

Hi I-fathy,

You can apply Conditional Formatting to the Column &/or Rows that the Pivot sits on & have the rules apply changes based on what you've asked for.

I've attached a quick example.

If you change the values in Cells A8-A10 to either RED, ORANGE OR BLUE, the Cell colour will change.

Cheers

[i-fathy]

Hi noboffinme,
i don't know if it is just me, as i cant get if what you said should be what i want

in your example i didn't find any charts, just pivot table, however im asking about pivot charts.

i attached an example of the chart im talking about.

in my chart, i want to color the bar of each player on basis of the team name next to each player name, from the source table

[noboffinme]

Hi I-fathy,

Sorry, my mistake, I would suggest for a chart that a macro be used to change the Chart bar colour relative to the Table for your names.

Are you familiar with writing VBA?

[i-fathy]

Im not sure how to do it, could you help me?

[noboffinme]

Hi I-fathy,

I got some of the way there but am stuck with one part & I hope that someone else in the forum can help.

I am trying to get the macro to match the colours in Column 'B' against the name is the same colour applied to the person on the chart.

The problem is the line where 'BLUE appears as the code doesn't like my 'Cells.Range("B2").Interior.Color' code.

I think it wants the same but with an 'mso' value.

Can anyone assist I-fathy with this?

Please Login or Register  to view this content.

[Andy Pope]

If you change the layout of your pivot table you can do it automatically.

Set the overlap of columns to 100.
Format each of the team colours as required.

Now use the column field to filter the teams.

[i-fathy]

@noboffinme thanks a lot for your effort to help What Andy suggested seems to do the job

@Andy many thanks it seems to work by just putting the team columns in "Legend fields", i will test it on my main workbook and tell you if something isnt right

but what do you mean by setting overlap of the columns to 100?

[Andy Pope]

select one of the columns in the new chart and display the format dialog.
On the Series Option tab is the Overlap setting. set it to 100. This make all the columns in a category appear infront of each other rather than side by side.