identifying a chart column individually in code

Let me try a different approach.
I currently use a clusterd column chart that is created automatically
with code.
Presently, I need to select each column manually to format them
according to a convention that we like at work.
Each column that represents a subset of a given group is colored to
represent the group of which it is a subset.

Each column that is of a different group gets a different color.

I think I can automate this process if I can tell the code to look at
column 1, for example.  Then column 2, and so forth through the entire
set of columns.  If I can do that, I can provide an IF then statement
in the colorindex definition that will color it according to the proper
grouping.

Is there any way to either define a numbering sequence to the columns,
or is there already one that I can tap into?

TIA

0
3/21/2005 8:19:20 PM
excel 39879 articles. 2 followers. Follow

3 Replies
538 Views

Similar Articles

[PageSpeed] 8

Hi,

The following code will change the colorindex of every column in a chart.

Sub x()
     Dim chtTemp As Chart
     Dim intSeries As Integer
     Dim intPoint As Integer

     With ActiveChart
         For intSeries = 1 To .SeriesCollection.Count
             With .SeriesCollection(intSeries)
                 For intPoint = 1 To .Points.Count
                     .Points(intPoint).Interior.ColorIndex = _
intSeries * intPoint + 4
                 Next intPoint
             End With
         Next intSeries
     End With

End Sub


Cheers
Andy

papa jonah wrote:
> Let me try a different approach.
> I currently use a clusterd column chart that is created automatically
> with code.
> Presently, I need to select each column manually to format them
> according to a convention that we like at work.
> Each column that represents a subset of a given group is colored to
> represent the group of which it is a subset.
> 
> Each column that is of a different group gets a different color.
> 
> I think I can automate this process if I can tell the code to look at
> column 1, for example.  Then column 2, and so forth through the entire
> set of columns.  If I can do that, I can provide an IF then statement
> in the colorindex definition that will color it according to the proper
> grouping.
> 
> Is there any way to either define a numbering sequence to the columns,
> or is there already one that I can tap into?
> 
> TIA
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
3/21/2005 9:19:10 PM
Andy,
That gets me much closer to where I need to be.  Is there a way, as I
am going through the loop, to refer back to the row of data that the
individual column is derived from?

What I am trying to accomplish is this:
I have a table that resembles (in part)

          A     B           C
1        1     1A         10
2        1     1C         15
3        1     1D         9
4        2      2A        12
5        3     3A         17
6        3      3B         4
7        4      4D         14

What I want is to have the data from Column B along the x axis while
the values in Column C ploted as bars with the values along the Y axis.
However, I want all of the bars that correspond to rows with a a common
value in Column A to be the same color.  In this example rows 1-3 would
be red, row 2 would be blue, rows 5 & 6 would be green, and so on.

So in order to get your code to work, I need to be able to get the
point reviewed in the loop to refer back to its source row.

Did that make sense?

Thanks for your help.

0
3/21/2005 11:26:45 PM
Hi,

In order to link back to the cells you will need to parse the series 
formula. See John's tips on this.
http://j-walk.com/ss/excel/tips/tip83.htm

Another approach maybe to use extra data series to plot specific 
colours. See Jon's example of conditional charts to see if its 
appropriate for your problem.
http://peltiertech.com/Excel/Charts/ConditionalChart1.html

Cheers
Andy

papa jonah wrote:
> Andy,
> That gets me much closer to where I need to be.  Is there a way, as I
> am going through the loop, to refer back to the row of data that the
> individual column is derived from?
> 
> What I am trying to accomplish is this:
> I have a table that resembles (in part)
> 
>           A     B           C
> 1        1     1A         10
> 2        1     1C         15
> 3        1     1D         9
> 4        2      2A        12
> 5        3     3A         17
> 6        3      3B         4
> 7        4      4D         14
> 
> What I want is to have the data from Column B along the x axis while
> the values in Column C ploted as bars with the values along the Y axis.
> However, I want all of the bars that correspond to rows with a a common
> value in Column A to be the same color.  In this example rows 1-3 would
> be red, row 2 would be blue, rows 5 & 6 would be green, and so on.
> 
> So in order to get your code to work, I need to be able to get the
> point reviewed in the loop to refer back to its source row.
> 
> Did that make sense?
> 
> Thanks for your help.
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
3/22/2005 10:54:55 AM
Reply:

Similar Artilces:

Deleting "hidden" charts on a worksheet
Hi everyone, I've been playing around w/ a worksheet that contains data and charts, inserting and removing multiple rows at a time. Unfortunately, I did not change the properties of the charts and they were all set to "Move and Size w/ Cells". When I deleted a series of rows, these charts disappeared and left a line across the spreadsheet where they used to be. I've tried everything to get rid of them (because they are now calculating incorrectly and giving me error messages), including deleting the rows where they appear, trying to select them on the sheet, but nothing w...

counting the values in a column of cells
To anyone who can help! I am trying to count up the values in a column of cells that contain formulas so i can give a sub total at the bottom of the sheet. I am trying to do this using vb. Does anyone have the code to do this? thanks, bob from long island, n.y. Sub countformulas() mysum = 0 For Each c In Selection If c.HasFormula Then mysum = mysum + 1 Next MsgBox mysum End Sub "bob" <rsalzer@ureach.com> wrote in message news:0b7201c36b1e$3c63f300$a501280a@phx.gbl... > To anyone who can help! > > I am trying to count up the values in a column of cells > that ...

Pivot chart and Total row
Hi All I have a Pivot table and attached Pivot chart (all good so far). However, there are three columns (drop column details here) which are the two columns of data and a grand total column (showing the average of my two data columns) When I turn it into a pivot chart it does not display the grand total column as well (is there a way to do this). I have tried to go into the chart options but these are locked. I can copy and paste the results but this is a bit clumsy. Any suggestions - any help greatly appreciated Kind regards Rexmann PS using excel 2003 ...

How do I add column A data labels to a scatter of columns B and C?
I have made a scatter plot using columns B and C as my x and y coordinates. I would like to use the data in column A to label my points. I already have the XY chart labeler 97 installed and selected, but I don't know how to get the data labels to show up on my graph. Thank you! Hi Christina - There are a couple of well written Excel add-ins you can download to do this: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Both are free downloads, they install easily, and they integrate into Excel's interface very nicely. They l...

Prepending values in a column with "\"
I would like to prepend all text values in a column with "\". What is the easiest way to do this if there are thousands of rows? Assuming column A has the text values. In B1 enter ="\" & A1 Double-click on fill handle of B1 to copy down. Copy then paste special(in place)>values>OK>Esc. Delete original column A if desired. Or you could use a macro to do in place with no formulas. Sub Add_Text_Left() Dim cell As Range Dim moretext As String Dim thisrng As Range On Error GoTo endit Set thisrng = Range(ActiveCell.Address & "," &...

how to I block text not to break across columns or pages ?
I want to keep certain text together and not have it split apart over columns or on a new page: like several lines comprising an address in an address book -- I want to keep it all together. Either apply a style with the Keep With Next paragraph format option to all but the last of the paragraphs you wish to keep together or Use Shift+Enter to make a line break within the same paragraph -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MV...

Fixed Number of Columns
After manipulating data in Excel I would like to copy and paste the data in a text editor. The data should be in fixed format, for example integer numbers should end in columns with multiples of 5. The text is then used as input to an old fashioned program which requires that the data be aligned I changed the font in Excel to Courrier New and then selected a column width of 5. When I copy the data to a text editor it copies some tab marks and the columns do not align. How can I make things align in the text editor? Vino, You can use a macro: copy the code below, then paste into a module...

How do I delete chart superimposed on Excel document?
Created Excel document. Print preview did not show gridlines. In attempting to get gridlines, somehow got into charts. A chart was created and is superimposed on original document. How do I get rid of the chart? How do I get gridlines on to original document? Oz Right-click on the chart and Edit>Clear>All To print gridlines.................File>Page Setup>Sheet Check "gridlines" and uncheck "draft quality". Gord Dibben MS Excel MVP On Fri, 4 Aug 2006 14:27:01 -0700, Oz Osborn <Oz Osborn@discussions.microsoft.com> wrote: >Created Excel docume...

Code that Runs nightly to export folder into a csv
I had posted on other forums but I haven't gotten a reply and really hoping someone here can help me or point me to someone who can. I was wondering if you can give me any information on how I can do this. I am not familiar with VBA programming other than excel recording macros and editing them slightly so sample code would be greatly appreciated. The task I am trying to perform is as follows: Every night, I would like to export all contents of a single folder "XXX" into a single .csv file and then move the contents to another folder named xxxx_mmddyy . Any ide...

Locking a column. Excel, 2002.
I don't know if locking is the correct phraseology and perhaps that is why I can't find an answer. In column A are the numbers, 1, 2, 3, and so on and I want these numbers to remain stationary (they are the rank ordering) when I do a sort on another column (say H). -- Regards Ron Badour Select the range that you want to sort, but avoid column A. Then sort your data. If you let excel guess at the range, it'll think that you may want all the contiguous columns sorted. Personally, I would sort that column, too. But I'd fill it with a formula like: =row()-1 (headers...

Chart: range info from seriescollection?
I would like to fill a combobox (Rowsource) with data that are used in for instance the XValues or Values of a Seriescollection of a graph. "ActiveChart.SeriesCollection(1).Values" does not give the right type (I was hoping for a string type of info like "Sheet1.Range(a1:a10)"). How to get a Range to be used as Rowsource for my combobox??? Thanks for your suggestions, JB The Values and XValues will return an array of values. To get the range address like this, you will need to parse the series formula. Or you could use John Walkenbach's class module for extractin...

Is it possible to save an excel chart as .emf?
I would like to save an excel chart as .emf instead of .jpeg or .gif using the script below. Is this possible? Sub SaveChartAsGIF () Fname = ThisWorkbook.Path & "\" & ActiveChart.Name & ".gif" ActiveChart.Export FileName:=Fname, FilterName:="GIF" End Sub ...

Step change charts
Help, I am trying to create a bar charts by year but only want to show increases and decreases through the middle years as step changes i.e. 1999 100 2000 +10 2001 -30 2002 +40 2003 120 How can I do this? What I should see is the first vertical bar being 100 then from the 100 level an increase of 10 to get to 110 then a decrease bar of -30 (from the 110 level) to get to 80 etc etc. For a tutorial, see the Excel | Charts | Waterfall Chart page of my web site. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Cus...

Excel Bar Chart with many elements Example
I am working with a survey database where one field represents one question in the Survey. Some questions allow the user to enter multiple answers. This data isn't normalized but I have t work with what I've got. Anyway I tried to create a chart in Acces with the wizard but got the error: "Cannot have more than elements in the chart". Anyway I attempted to recreate the char in Excel but my chart looks crammed and canno print entirely within one page. Does anyone know how or have a example of how to create a bar chart with many elements in a clea and consice manner T...

Can no longer cut/paste ranges in chart "edit data" fields?
In Excel 2007 I can no longer cut/paste cell ranges into the x and y datasets using the edit data tool. Is there a way to turn this back on? Also the chart range resets itself to the current length of the data, however, in many instances I want to set an exact future range. Is there a way to have excel not reset the range to what it "thinks" I want. > In Excel 2007 I can no longer cut/paste cell ranges into the x and y > datasets > using the edit data tool. Is there a way to turn this back on? I have no problem doing what you can't. Could you provide more det...

Coding problems with User defined Visible Property
Hi I have a report that my users supply to seavel groups. Some do not require certain fields so these will be hidden by using a checkbox on the relevant form. At the moment, I am using the following in code builder: Me.txt[recordNumber].Visible = Forms![Pdoptions]!chk[recordnumbersvisible] Which isn't doing the job. I've checked all the names and they are correct. This code gives me eaither a syntex error, or an expected end error, but I can find no problem. The code is sitting in the "on format" property of the detail section header, of the report I need this to work in. If...

How does one print the columns from a Visio ERD diagram?
I see them but can't print them. ...

Why are my columns and rows reversed when I open Excel
When I open my Excel spreadsheet the columns, rows and scroll bars are reversed?? What's happened? Tools|options|International Tab Uncheck that "view current sheet right-to-left" Deb Web wrote: > > When I open my Excel spreadsheet the columns, rows and scroll bars are > reversed?? What's happened? -- Dave Peterson ...

Merging queries by column in access
Hi, I have a series of queries which get data aggregated by year, e.g. select year(date), min(var1), avg(var1), max(var1) from table where (ridiculously complicated set of conditions) group by year(date) select year(date), min(var2), avg(var2), max(var2) from table where (entirely different ridiculously complicated set of conditions) group by year(date) etc etc There are six queries like this so what I want is to amalgamate these columns together into one results table (and also into one query so users can just run one stored query rather than six!) as follows: year, m...

Excel 2000... Missing row and column designators.
Ho do you mean 'Tools - Options - View' and check 'Row/Column headings' -- Regards Frank Kabel Frankfurt, Germany "Clarence" <carowe@cnyconnect.net> schrieb im Newsbeitrag news:2ee7401c46c21$a45aef40$a501280a@phx.gbl... > ...

can not see chart
I created a simple chart. For some odd reason the chart is all gray. I can only see the chart if i select the chart object. Please help! Thank you! Hi, Via the menus Tools > Options. On the View tab select Obejcts Show All. Cheers Andy Nick wrote: > I created a simple chart. For some odd reason the chart is all gray. I can > only see the chart if i select the chart object. Please help! Thank you! -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

update the same cell into another column forming a list on opening
hi there, i have made a spreadsheet to enter data and then view the resulting statistics that are generated, i have one cell that changes in value after entering the data, and i would like to keep a record of what values this cell has been, i was after a formula that would update that single cell's value into a column forming a list each time the spreadsheet was opened or the value in the cell changed, like a field perhaps, this would fill up the column with a list of results from which to generate more stats and a graph etc. any help would be great, thank you -- thank...

Compare and align rows of multiple but differing columns of data #2
I have a table of 4 columns. One column has county names, one population and the other two have data relative to the county name for the given year. Each year a new set of 4 columns is generated. However, some but not all of the county names are in the subsequent year. Same thing for each successive year. How can I: 1) Match the counties from year to year so they align in a row with corresponding data? What happens if year being used for comparison doesn't have county appearing in later year? 2) Make master list of counties (in left column) for all that have appeared in the data sets...

Excel: keeping rows together when ordering order of left column
in my spreadsheet I want to order the left hand column (in ascending numbers) but when I do I want the rows to move with cells too, keeping the items of data in the rows with the original items of data in the left hand cells when they move place Select the data say A1:J10 and then from menu Data>Sort> --If your data has headers then select 'My data range has' headers .. --Select 'Sortby'..and hit OK -- Jacob "Tangojon" wrote: > in my spreadsheet I want to order the left hand column (in ascending numbers) > but when I do I want...

Is there any way to use the MATCH function with more than 1 column
For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value 215, but my data set spans more than one column. My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) returns an #N/A error. What can I do? Do you want both the row and column ? Two separate values? -- Gary's Student "Mr. Snrub" wrote: > For example, =MATCH(215, Y3:Y51, 0) will search from Y3 to Y51 for the value > 215, but my data set spans more than one column. > > My data set ranges from A3 to Z51, and the function =MATCH(215, A3:Z51, 0) > returns an #...