Need VBA code to plot Horizontal Bands in the background of a line chart in Excel 2007

Question

Looking for help to plot horizontal bands highlighting "target" ranges on a line chart.  Line chart XValues are dates; the data are values 0-100.  Typically three or four horizontal bands, with the same min/max values.  For instance:  Low == 0-41, Mid == 42-69, High == 70-100.  

A good example is shown by Mr. Peltier using the Excel User Interface at (http://peltiertech.com/WordPress/horizontal-bands-in-background-of-excel-2007-chart/)   However I need VBA code -- I have multiple charts, and generation of these charts is repeated quarterly, with variations in the bands. Thanks much.

Answer 1

Mr. Mehta, Again, referring to Mr. Peltier, I would like to do what he's doing, but with VBA code.   I see I confused the issue... the point of my last sentence was that I need VBA code... I don't need help with the repeatability.   (I'm using Excel 2007 and the macro recorder does not assist with this.  I do appreciate the information at the site you referenced, and perhaps will use that for another application.)

Answer 2

Use the macro recorder to get the necessary code.  Share it here together with additional info about the different charts and variations in bands and someone should be able to help you.

If I were in your shoes, I'd figure out a way of creating the bands w/o VBA (using named ranges / named formulas / ??) and then use VBA to simply apply this technique to each chart.

For the use of named formulas in charts see

Dynamic Charts

http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

---

Tushar Mehta (Technology and Operations Consulting)

www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)

Microsoft MVP Excel 2000-Present