Greetings,

I have a series of line charts (time-x vs value-y) with axis limits and
data ranges updated automatically. I need to come up with a way to
identify certain periods of time with shading. But I don't wish to
simply draw rectangles because I don't want to have to edit the
location of the shapes every time new data is added. Also, I have
about 30 charts to create and maintain.

So ...

1. Is there a away to associate shape position/size to axis values?

2. If not, are there other charting options that would allow me to
plot time vs value overlayed by bars/columns with widths and locations
dictated by the same time-x axis? In other words, I need the location
and width of the columns to be determined by x axis data.

Thanks in advance!


--
William DeLeo
------------------------------------------------------------------------
William DeLeo's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread...hreadid=496382

1. Is there a away to associate shape position/size to axis values?
This can be done but it is rather complex. I don't think you want to go there.

I suggest checking out Jon Peltier's site:
http://peltiertech.com/Excel/Charts/...ticalBand.html

If you can't find what you need there then perhaps the following:

Although I've never had occasion, I think I would have a 2nd series that has
exactly as many points as the first and make the chart type Column (you can
have more than one chart type plotted). For the area you don't want shaded,
plot zeros, and for the area you want shaded, plot the max chart scaling.
Alternatively, plot a value greater than the max scaling and unselect the
AutoScale option so it doesn't adjust to this value (only shows what you
want). Set the gap width to zero so that it blends into one shaded zone.

You will have to programmatically manipulate the series to stay in sync with
the first series including adjusting for changes in min and max scaling if
they occur. I don't have code that does this but you can use the macro
recorder to get some basic info. To get started:

- Unselect the Y axis Autoscale option and enter an appropriate in place
- Add a series to your chart that has exactly as many points as the first
- Change its chart type to Column
- Right click the series and select Format Data Series
- Select the Patterns tab
- In the Border frame, set the series Border to None
- In the Area frame, select the dersired colour
- Select the Options tab
- Set the gap width to zero

Now play with the values of the new series. Opposite the values in the first
series where you want shading, change the values to = the max scaling value.
All other values set to zero.

Hope this was of help.

Regards,
Greg

"William DeLeo" wrote:


Greetings,

I have a series of line charts (time-x vs value-y) with axis limits and
data ranges updated automatically. I need to come up with a way to
identify certain periods of time with shading. But I don't wish to
simply draw rectangles because I don't want to have to edit the
location of the shapes every time new data is added. Also, I have
about 30 charts to create and maintain.

So ...

1. Is there a away to associate shape position/size to axis values?

2. If not, are there other charting options that would allow me to
plot time vs value overlayed by bars/columns with widths and locations
dictated by the same time-x axis? In other words, I need the location
and width of the columns to be determined by x axis data.

Thanks in advance!


--
William DeLeo
------------------------------------------------------------------------
William DeLeo's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread...hreadid=496382

Thanks so much! I will check out everything you suggest today. I find
experimenting with this sort of charting frustrating, so your specific
instructions will make a huge difference. Thanks again!!!


--
William DeLeo
------------------------------------------------------------------------
William DeLeo's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread...hreadid=496382