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!

--
anantathake
-----------------------------------------------------------------------
anantathaker's Profile: http://www.excelforum.com/member.php...fo&userid=1813
View this thread: http://www.excelforum.com/showthread.php?threadid=38006

This macro would assign a unique color to each country. For the macro t
work you have to select the chart and then execute the macro, repea
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 Su

--
anilsolipura
-----------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...fo&userid=1627
View this thread: http://www.excelforum.com/showthread.php?threadid=38006

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!!

--
anantathake
-----------------------------------------------------------------------
anantathaker's Profile: http://www.excelforum.com/member.php...fo&userid=1813
View this thread: http://www.excelforum.com/showthread.php?threadid=38006

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


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380067

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!


--
anantathaker
------------------------------------------------------------------------
anantathaker's Profile: http://www.excelforum.com/member.php...o&userid=18133
View this thread: http://www.excelforum.com/showthread...hreadid=380067

Yes it can be done.

use

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


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=380067

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


--
anantathaker
------------------------------------------------------------------------
anantathaker's Profile: http://www.excelforum.com/member.php...o&userid=18133
View this thread: http://www.excelforum.com/showthread...hreadid=380067