How do I put multiple colors in a chart's plot area

For instance, if I have a single series chart with Y axis from 0% to 5%, how
do I put a red color from 0 to 2%, green from 2 to 4% and blue from 4 to 5%?

TonyU1234

I am not entirely sure what you need but can offer the following. I am
assuming you have a simple line chart here.

Two options

(1) You can manually select data points and assign them a colour dependent
upon value. Simple to do, but laborious if lots of data points

(2) This VBA code may help. In simple terms, it loops through each data
point, tests the value of the datapoint, and assigns a colour of red 2%:
green 2% - 4%; blue 4%+.

Sub ColorPoints()
Dim i As Long
Dim ChartData As Variant
ChartData = Sheets("Sheet1").ChartObjects(1).Chart.SeriesColle ction(1).Values
For i = 1 To UBound(ChartData)
With Sheets("Sheet1").ChartObjects(1).Chart.SeriesColle ction(1)
Select Case ChartData(i)
Case Is 0.02
.Points(i).Border.ColorIndex = 3
Case Is 0.04
.Points(i).Border.ColorIndex = 10
Case Is 0.04
.Points(i).Border.ColorIndex = 41
End Select
End With
Next i
End Sub

You may have to change some of the references to make this work e.g. the
worksheet / chartobject references etc.

Hope this helps.

Regards


Alex
"TonyU1234" wrote:

For instance, if I have a single series chart with Y axis from 0% to 5%, how
do I put a red color from 0 to 2%, green from 2 to 4% and blue from 4 to 5%?

Hi,

If you want colour banding on the plot area you can produce this effect
by adding 3 dummy series and plotting them as stacked column.
The values for each point in each series will be 2% , 2% and 1%.
Format the columns to be the correct colours also remove the border.
On the Format dialogs Options tab set the Gapwidth to 0.

Cheers
Andy

TonyU1234 wrote:
For instance, if I have a single series chart with Y axis from 0% to 5%, how
do I put a red color from 0 to 2%, green from 2 to 4% and blue from 4 to 5%?

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

An easier, non-VBA technique is described he

http://peltiertech.com/Excel/Charts/...nalChart1.html

I think the OP wanted the background colored, though, not the data series.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"Alex" wrote in message
...
TonyU1234

I am not entirely sure what you need but can offer the following. I am
assuming you have a simple line chart here.

Two options

(1) You can manually select data points and assign them a colour dependent
upon value. Simple to do, but laborious if lots of data points

(2) This VBA code may help. In simple terms, it loops through each data
point, tests the value of the datapoint, and assigns a colour of red 2%:
green 2% - 4%; blue 4%+.

Sub ColorPoints()
Dim i As Long
Dim ChartData As Variant
ChartData =
Sheets("Sheet1").ChartObjects(1).Chart.SeriesColle ction(1).Values
For i = 1 To UBound(ChartData)
With Sheets("Sheet1").ChartObjects(1).Chart.SeriesColle ction(1)
Select Case ChartData(i)
Case Is 0.02
.Points(i).Border.ColorIndex = 3
Case Is 0.04
.Points(i).Border.ColorIndex = 10
Case Is 0.04
.Points(i).Border.ColorIndex = 41
End Select
End With
Next i
End Sub

You may have to change some of the references to make this work e.g. the
worksheet / chartobject references etc.

Hope this helps.

Regards


Alex
"TonyU1234" wrote:

For instance, if I have a single series chart with Y axis from 0% to 5%,
how
do I put a red color from 0 to 2%, green from 2 to 4% and blue from 4 to
5%?

This web page describes a technique, which could probably be adjusted to
give the OP the desired effect:

http://peltiertech.com/Excel/Charts/...ticalBand.html


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"Andy Pope" wrote in message
...
Hi,

If you want colour banding on the plot area you can produce this effect by
adding 3 dummy series and plotting them as stacked column.
The values for each point in each series will be 2% , 2% and 1%.
Format the columns to be the correct colours also remove the border.
On the Format dialogs Options tab set the Gapwidth to 0.

Cheers
Andy

TonyU1234 wrote:
For instance, if I have a single series chart with Y axis from 0% to 5%,
how do I put a red color from 0 to 2%, green from 2 to 4% and blue from 4
to 5%?

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info