# Thread: Color the Bars - II (2000)

1. ## Color the Bars - II (2000)

Hi there,

I had a posting, Color the Bars (59938 on 2 Aug) where the goal was to have the colors of bars in a chart depend on the data point's values. This was for some teachers I had done a class for. So, for example, students with grades from 91-100 should have blue bars (for example), 81-90 would be green, 71-80 would be tan, and 70 and below would be red.

Only response that I got was from Hans Pottel (thanks) but that opened a few more questions. See the various postings on that thread.

However, something that Hans suggested made something tick in my head that actually provided a solution using other postings. It's not elegant and not the best approach (in my opinion) but it does the job.

The original problem assumed that students' names were column headings (or row headings) and their grades were directly below them in the same row. I'd still like to solve that problem. If anyone has a solution, that would be great and still appreciated.

What clicked in my head was converting the grades from 1 row to (using above) 4 rows - 1 for each range of grades. So each student only has 1 entry below his/her name corresponding to the row with the range in which the grade falls; the rest of that col is 0 or blank. All we have to do is get the grades from the original form (all in 1 row) to this form (actually not too hard). Now the chart wizard picks up the non 0 grades in the same range and picks the same color. Different range, different color.

We then applied another tip from the lounge. The bars were 'aligned' within each students' name on the x axis depending on whch range they fell in (lowest range, to the left; highest range to the right). The 0 (or actually blank) grades were not visible. Playing with the gap width and overlap took care of things.

If anyone can solve the original problem as stated there, let me know. For now, this will have to do.

Fred

2. ## Re: Color the Bars - II (2000)

Fred

I deal with a lot of school grades and I constantly run into limitations of plotting in XL. I pass problems that I cannot deal with onto a colleague who is a wiz in S+, that is much more powerful and can usually do what I want. My colleague assures me that what you want to do would be straightforward in S+. However S+ is quite costly but there is a much cheaper, possibly freeware or shareware, called R. Perhaps a bit of web searching would be worthwhile.

3. ## Re: Color the Bars - II (2000)

Hi Mike,

Thanks for the ideas. Probably going with S+ or even R is not an option - cost on the one and learning curve on both (learning curve with teachers?). I might look into R for my own benefit.

I think the problem is not hard to solve but beyond my means. If you can, look back on the original thread. I think there were only 3 items there - my original posting, Hans's posting, and my reply to him. I would think that, given a named range to be used like a lookup table (upper limit of test scores in 1 col and colors in the next; OK have to make sure the colors are valid names like blue and red and not cats and dogs), it would not be hard to loop thru the bars of a chart, find the data point value associated with the bar, find the data point's associated color from the lookup table, change the bar color (I think I recall a thread where color numbers were derived from names). But I know I can't do this. My Excel VBA is limited and almost non-existent when it comes to charts.

By the way, your tip on dynamic ranges (about 2-3 weeks ago) helped solve my daughter's problem on making charts using different sets of cells. I was hoping she'd post the solution but I don't think she's even taken my example and applied it to her specific needs. She's said she'd do it a few times, the latest being just about an hour ago when we spoke.

Fred

4. ## Re: Color the Bars - II (2000)

Fred,

I have been away for a while, not being able to continue our discussion. I don't know if I follow exactly what you want, so correct me if I misunderstood. Below you can find some code that starts from a range in a spreadsheet (namely A1:B10), containing the values 10, 20, ... up to 100 in column A (so from A1 till A10) and in column B, next to e.g. 60, I put the number of students with results between 50 and 60 (not including 60). The code below starts from this data, makes an embedded column chart on Sheet1 and then loops through the columns giving it a different color depending on the value in the B column.
This is maybe not what you want to do, but it must give you an idea of what can be done. Personally, I have written some code for my employer that uses an equivalent histogram as an overall picture of evaluation results of the employees in a certain function class and a colored bar then indicates the individual class for one employee. In this way, every employee received a personalized report of his evaluation, as compared to the large group of function holders to which he/she belonged.

Put some data in range A1:B10 of Sheet1 (more or less equivalent to the data I described) and paste the macro in a module of the VBE. Then run the macro from Tools >> Macros >> Macro >> Macro1 and have a look to the output. Maybe, this can be the basis for the next step.

<pre>Sub Macro1()
Dim R As Range
Dim i As Integer
Set R = ActiveSheet.Range("A1:B10")
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=R, PlotBy:= _
xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.HasLegend = False
ActiveChart.SeriesCollection(1).Select

For i = 1 To R.Rows.Count

ActiveChart.SeriesCollection(1).Points(i).Select

Select Case R.Columns(2).Cells(i).Value
Case 0 To 30
Selection.Interior.ColorIndex = 38
Case 31 To 60
Selection.Interior.ColorIndex = 6
Case 61 To 100
Selection.Interior.ColorIndex = 15
End Select

Next i
End Sub
</pre>

5. ## Re: Color the Bars - II (2000)

An alternate way is to use different series for the different colors. Have the base data in the first column. The second column has just the blue data points. The third has red and so one. Make a bar chart from the 2nd column on. Each series gets a different color. Adjust the bar widths and overlaps so that the bars plot on top of each other. This won't be a problem since at each data point you have only one column with data in so only one bar will plot.

6. ## Re: Color the Bars - II (2000)

Hans,

Thks for getting back to me on this. Hope the time away was restful.

I am attaching a file here that shows what I'm trying to do. On Sheet1 is a set of cells with student names in row 1, grades in row 2, and a resulting chart. If you look at the VBE, you'll see a macro that does some of the stuff you wrote about in your reply to the original thread and goes a few steps further. However, that didn't get me very far and the chart is not what I'm after. At best, what I started doing, with the macro recorder on, was selecting bars and changing their colors. Definitely time consuming in a class of any appreciable size.

If you look at Sheet2, you'll see a chart that has what I want - color of the bar depending on what range the student's grade is in. But note that the set of cells with names and grades is expanded from what it was on Sheet1: 3 rows for the ranges of grades, instead of 1, plus the row for the names. It probably would not be much work to create the 3 rows of grade ranges on Sheet2 from the 1 corresponding row on Sheet1 (altho I've not given it a lot of thought yet).

Adding in an adjustment of the gap width and overlap gives the 2nd chart on Sheet2. I believe there was a thread on this lounge that adjusted these 2 values to get rid of the 0-height bars corresponding to the 0 values in A1:G4.

I think this is similar to gibbindr's suggestion.

So what I'd ideally like to do is get the chart on Sheet2 (ideally the 2nd chart but 1st one might do also) with the input on Sheet1.

I liked your approach of creating a chart from your previous response and gave it a whirl. It did not seem to work as I expected it. At the very least, following your code, I expected the case statement to give me bars of different colors. All mine were pink. I'll attach that in a separate posting. (It seems not only is there a 100K Max on attachments but also a single file limit even if multiple files are less than 100K).

Thanks much.

Fred

7. ## Re: Color the Bars - II (2000)

Hans,

Scratch the attachment in this posting. I realized that my col B data were all less than 30 so that all my bars were the same color bcs of the way you set up the select case statement. I changed a few points and got different colored bars. Thanks.

Given this adjustment, I think your code is very close to where I need to be.

The other significant item might be that the teachers want the bars to be specific colors. So what I mentioned in one of the postings (probably my first one in the original thread) was that they'd create a 2-column range with upper bounds of the grade range in, say, col A, and the corresponding color in col B - kind of like a lookup table. This would allow them to choose the color for each grade range. I realize this might be a pain to use for the ColorIndex statement since the color would have to be valid ("Blue" not "Blew") and would have to be converted to a number. Alternatively, they could let Excel initially pick the color and then go into the legend to change the color for the corresponding grade range. So they only have to change x colors (number of grade ranges) instead of y colors (number of students). Or they can live with what Excel picks.

Thanks much.

Fred

8. ## Re: Color the Bars - II (2000)

One poster came close to the best way to do this. Multiple columns of data, with either zero or the grade, if it fell within certain bounds. You can put the cutoff for the grade at the top of the column, with if statements below that either return the grade or zero. But then the poster had to fiddle with overlaps and spacings.

If you make this a stacked column chart, instead of a clustered column chart, your bars for each student (all zeroes except for a single non zero bar) align perfectly in a stack, with no need to adjust overalp and spacing, and the zeroes don't show.

- Jon

9. ## Re: Color the Bars - II (2000)

Jon,

Thks for the thoughts.

Actually, the poster who had to fiddle with overlaps and spacings was me.

gibbindr suggested the solution of multiple rows for the ranges (or cols depending on your orientation) where only 1 row gets filled in per col (where col is the student name). I came up with that at the same time and posted that with the attachment showing the chart before and after fooling with the spacing and overlaps to get the desired affect.

Your suggestion of a stacked column chart helps advance the solution 1 step further - no fooling with overlap and spacing as you stated.

I did notice that the category (X) axis provides a lot of room per student, even I rotate the names to 90 degrees. Is there a way to decrease the amount of room on the axis allocated per student?

Still looking for the ideal solution of the chart per Sheet2 but the input per Sheet1 in my attachment earlier in the thread.

fred

10. ## Re: Color the Bars - II (2000)

Fred,

My code starts from the assumption that the names of the students are in column A and their results in column B. So, only two columns are needed for your data. This is the most optimal arrangement of your data as there will be no empty cells. However, the disadvantage is that there is no way to use the legend in the chart as you actually showed it in your attachment. Excel does not allow to change the LegendEntries, it only allows to change its format. This means that you have to delete the legend if you use this arrangement of your data. An alternative would be to add a textbox to your chart, add another small square textbox in the first textbox, with background color e.g. yellow, and next to that another textbox, with contents '70-80' etc. In other words, you make your legend manually. If you record this, step by step, then you obtain a macro making a legend. Using the macro I posted earlier will keep the amount of room on the axis allocated per student to the minimum. I know this is not really the easy way but I don't know of another way to do it in Excel.

11. ## Re: Color the Bars - II (2000)

Hi Hans,

Thks for the reply. I need to go back and look at the code you sent earlier using Col A for the student's name. I think your previous email used Col A for the grade range. At least that's the way I took it. One thing I didn't understand from your code was the following set of statements:
ActiveChart.SeriesCollection(1).Delete
.....
ActiveChart.SeriesCollection(1).Select

Why are there these 2 statements - one to delete and then one to add? It doesn't seem like anything gets done in between the 2 statements that would necessitate the Select. Also, what is the (1) for - is there also something for (2), (3), etc.

As you can probably tell, my experience with Excel VBA as applied to charts is almost non-existent.

As far as changing the legend, I tried that also and could not find a way to change it. The idea of manually generating the legend did occur to me but I decided against that approach.

Maybe using the stacked column chart with many empty cells, as suggested by jon, might also be a way to go.

thanks

fred

12. ## Re: Color the Bars - II (2000)

Fred,

If you use names of students in Col A, then the code will work too. The ActiveChart.Seriescollection(1).Delete can be omitted in the code. The (1) is the number of the seriescollection. You can have a second, third etc. SeriesCollection if you want.

13. ## Re: Color the Bars - II (2000)

I've done a lot of work with VBA and the charts and it is a difficult subject and has a convoluted object to learn. I suggest reading the help files as they are usually good at answering your questions (but they do have errors). From within the VBA editor, put your cursor on the method in question (SeriesCollection) and press F1. I learned most of what I know from reading the help and writing sample code (plus lots of swearing and trial/error).

The data that gets plotted is stored in what Excel calls a "Series" which is a collection (you know that because of the 's' which 95% of the collections use, Names, Charts, etc.) So SeriesCollection(1) is the first set of data plotted. Charts can plot multiple data sets each of which is shown as a line on the graph. Typically you're storing an array of data to be plotted.

As to why the .Select, I can't say w/o seeing the code, but in some cases it's necessary to select the chart object before doing something with it, but not always. Most of the time it's totally unnecessary but if you look at code that comes from recorded macros, it's all over the place (that's one of many reasons why using these macros un-edited is a bad idea as they write crappy code and don't reveal other ways of doing things). If you're curious, just comment out that line and see if it still works, if not then put it back. Trial and error is a good teacher, you'll remember it more than if someone just told you the answer.

Deb <img src=/S/hello.gif border=0 alt=hello width=25 height=29>

14. ## Re: Color the Bars - II (2000)

Hi Hans,

I played around with your code a little more just now. changed Col A to student names. Works as you said. I guess I have to forgo the legend unless I do it with the "sparse matrix" approach of multiple rows of grades (with either a stacked column or a clustered column).

I also ran into a problem - run time error at
ActiveChart.SeriesCollection(1).Select

Since you said the ...Delete statement wasn't necessary, I commented that out and it worked.

Thanks for the help. My teacher/student is on vacation (I think until the end of Aug). I think between all the items on the lounge that I should be able to put together a solution for her.

Fred

15. ## Re: Color the Bars - II (2000)

Deb,

Thks for the info.

Hans's code is in one of the posts in this thread. So if you want to see it, check out his 11-Aug posting.

I'm still not sure about your statement of "SeriesCollection(1) is the first set of data plotted". I know that Excel can create multiple "lines" like a line-column chart. So would data for the line be SeriesCollection(1) while for the column be ...(2)? In the case of a column chart (like what I'm doing), are you saying that I could have multiple bars as ...(1) and yet other bars as ...(2)? If so, it would seem that, with VBA, you could create almost any kind of chart and with any number of data sets.

Based on what you said, it would seem that it should not be difficult to tackle my problem as originally posed: have a set of data with names and grades (and no blanks), then have a macro that turns this into a column chart (easy to do/record) and then go to a "lookup table" that gives the color for the bar based on the grade and change the colors appropriately. However, I think I will stick with the solutions that we've arrived at (either Hans's or mine with the sparse matrix approach).

I did try reading about the chart object model in Steve Roman's Programming Excel pub'd by O'Reilly. Convoluted is generous. I did try a few things but got nowhere fast.

fred

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•