bar graph colors?

Hello everyone,

I have data with 5 variables for 10 countries, and I have made a horizontal bar graph for each variable (so 5 graphs). This data is linked to another page, and it is divided by variable within the sheet. For example, the format is:

Country Variable 1
US ###
China ###
India ###
UK ###

Country Variable 2
US ###
China ###
India ###
UK ###

There is a macro that sorts each of this mini-tables, and then each graph draws from its respective variable table. The same 10 countries are in each table, but they are not in the same order, because they are sorted from largest to smallest so that each horizontal bar graph is sorted largest to smallest.

My question is, how can I make it so that each country has the same color bar in each of the 5 graphs? For example, the US has a red bar in each graph, regardless of its position. Is there a way to do this? I'm no VBA expert, but is there some sort of conditional macro I can do (any code someone has would be extremely helpful)? Is there an easier way? Thanks!!

This macro would assign a unique color to each country. For the macro to work you have to select the chart and then execute the macro, repeat this for all the charts you have.

In the code I put

If (PTS(I) = "US") Then
.Fill.ForeColor.SchemeColor = 8

that means if US , then color scheme is 8 (blue),

If (PTS(I) = "China") Then
.Fill.ForeColor.SchemeColor = 12

that means if China, then color scheme is 12

repeat this for all other countries, I code for 4 countries.

try the below code and let me know if you have any problems

Sub Macro5()
Dim PTS As Variant
On Error GoTo A:
ActiveChart.ChartArea.Select 'selecting chart area
ActiveChart.SeriesCollection(1).Select
PTS = ActiveChart.SeriesCollection(1).XValues 'extract the values
For I = 1 To UBound(PTS) 'for every point
ActiveChart.SeriesCollection(1).Points(I).Select
With Selection 'applying the color
.Fill.Visible = True
If (PTS(I) = "US") Then
.Fill.ForeColor.SchemeColor = 8
ElseIf (PTS(I) = "India") Then
.Fill.ForeColor.SchemeColor = 35
ElseIf (PTS(I) = "China") Then
.Fill.ForeColor.SchemeColor = 12
ElseIf (PTS(I) = "UK") Then
.Fill.ForeColor.SchemeColor = 18
End If
End With
Next
A:
If Err.Description <> "" Then
MsgBox "need to select chart for the macro to work"
End If
End Sub

Thanks so much! This worked really well. Two questions:

1. Is there a way to automate the selecting the graphs part? Like say I use this:

ActiveSheet.Shapes.Range(Array("Chart 2", "Chart 3", "Chart 8", "Chart 9" _
, "Chart 1", "Chart 4", "Chart 7", "Chart 10" _
, "Chart 11", "Chart 12", "Chart 5", "Chart 6")).Select

That is all of my charts. Is there any place in the code you sent me I can put this?

2. Where can I find out what code numbers represent which colors (like how 8 represents that light blue)?

THANKS AGAIN!!!

Yes you are correct, there is a way to automate looping through the charts. I added that code into the macro.

About the color number, I got it though macro recording and trial and error method.

Sub Macro5()
Dim PTS, ch_count As Variant
ch_count = ActiveSheet.ChartObjects.Count
For j = 1 To ch_count
ActiveSheet.ChartObjects(j).Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
PTS = ActiveChart.SeriesCollection(1).XValues
For I = 1 To UBound(PTS)
ActiveChart.SeriesCollection(1).Points(I).Select
With Selection
.Fill.Visible = True
If (PTS(I) = "US") Then
.Fill.ForeColor.SchemeColor = 8
ElseIf (PTS(I) = "India") Then
.Fill.ForeColor.SchemeColor = 35
ElseIf (PTS(I) = "China") Then
.Fill.ForeColor.SchemeColor = 12
ElseIf (PTS(I) = "UK") Then
.Fill.ForeColor.SchemeColor = 18
End If
End With
Next
Next
Range("a1").Select
End Sub

Thanks again!

Last question: The ten countries list could change. Instead of having

If (PTS(I) = "USA")

Can I make a reference to a cell that has the text "USA" in it? This would allow the list of 10 countries to by dynamic. I tried If (PTS(I) = "B236"), but it didn't work. Any ideas?

AGAIN, THANK YOU SO MUCH!

Yes it can be done.

use

if (PTS(I) = range("B236").value) then ' say b236 has USA
.Fill.ForeColor.SchemeColor = 8

It Works Perfectly! Exactly What I Needed! Thank You So Much!