bar graph colors?

[anantathaker]

Hello everyone,

I have data with 5 variables for 10 countries, and I have made
horizontal bar graph for each variable (so 5 graphs). This data i
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 eac
graph draws from its respective variable table. The same 10 countrie
are in each table, but they are not in the same order, because they ar
sorted from largest to smallest so that each horizontal bar graph i
sorted largest to smallest.

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

 

[anantathaker]

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

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

ActiveSheet.Shapes.Range(Array("Chart 2", "Chart 3", "Chart 8", "Char
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
can put this?

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

THANKS AGAIN!!

 

[anilsolipuram]

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

 

[anantathaker]

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!

 

[anilsolipuram]

Yes it can be done.

use

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

 

[anantathaker]

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