TR Forums

Dissonance · Fri Jan 16, 2009 5:00 pm

Color-coding bar graphs by manufacturer, model, or whatever, is a great way to make them more readable. However, it's royal pain, especially for hard drive reviews where I'm working with dozens of graphs and a deluge of results. Excel doesn't seem to have a mechanism to automatically color bars based on axis labels, but I imagine VB wizards would be able to code up something that would work. I hope.

What I want to be able to do is simple. After graphs have been created and sorted, I'd like a simple and easy way to color each bar associated with an axis label of XXX with a specified color, and I'd like the colors to cascade across all graphs in a workbook. Beyond the odd batch file, I haven't done any programming for years, so I'm putting the call out to you folks.

I'm running Excel 2002, and can provide a sample file for anyone up to the task of taking on this hopefully simple challenge. In return, I can offer my undying gratitude and the knowledge that you've made TR graphs that little bit easier to create. And perhaps some official TR swag, or something.

If you're interested, drop me an email. I'll also keep an eye on this thread.

Geoff

---
Geoff Gasior
The Tech Report
http://techreport.com

Usacomp2k3 · Mon Jan 19, 2009 12:55 pm

Sounds like fun. You've got mail.

EDIT: I think. Maybe it didn't go through.

Usacomp2k3 · Mon Jan 19, 2009 11:55 pm

So I got it pretty much figured out. Let me know if this works for you. (I got the original code from a web site, but I can't for the life of me find it again. I did make some fairly significant changes to that though.)
http://usacomp.homeserver.com/Site/TR/Book1.xls

Sub RunChartColoration()
Dim myChart As ChartObject, mysheet As Worksheet
Dim i As Integer, arValues As Variant
For Each ws In Worksheets
    For Each myChart In ws.ChartObjects
        myChart.Activate
        With ActiveChart.SeriesCollection(1)
            arValues = .XValues
            For i = 1 To UBound(arValues)
                .Points(i).Interior.ColorIndex = VLookupStuff(arValues(i))
            Next
        End With
    Next
Next
End Sub

Function VLookupStuff(ByVal name As String)
VLookupStuff = Application.WorksheetFunction.VLookup(name, Sheets("data").Range("names"), 2, False)
End Function

So basically:
*Have a range in some sheet Have that is a simple 2-column table with the names in one column, and the colorindex that you would like to use to represent that value in the next column.
*Give this range a name. In this example I have it in a sheet named "data" with the named range of "names". Edit the code to correspond with your choices.
*Now just run the macro (for exmple, by creating a button and assigning it the macro name, or by hitting alt-f8 and choosing it)

A quick explanation of the code:
This uses the vlookup function in excel to lookup the names and return the colors.
As you can see there are nested for loops that basically go through each worksheet in the file, and then selects each chart on that sheet. It then goes and looks at all the points in that chart, looks up what the value is, runs the function that then returns what color you want that to be, and then applies the color. It should be fairly straightforward.

I hope that this helps, and it's open to tweaking and customizing to what you need it to be.

Usacomp2k3 · Fri Jan 23, 2009 6:18 pm

And attempt #2:

Function VLookupStuff(ByVal name As String)
Dim c As Range
For Each c In Range("names")
    If c.Value = name Then
        VLookupStuff = c.Offset(0, 1).Value
        Exit For
    End If
Next
End Function
Sub RunChartColoration()
Dim myChart As ChartObject, mysheet As Worksheet
Dim i As Integer, arValues As Variant
For Each ws In Worksheets
    For Each myChart In ws.ChartObjects
        myChart.Activate
        With ActiveChart.SeriesCollection(1)
            arValues = .XValues
            For i = 1 To UBound(arValues)
                .Points(i).Interior.ColorIndex = VLookupStuff(arValues(i))
            Next
        End With
    Next
Next
End Sub

Damage · Wed Jun 03, 2009 3:09 pm

Ok, wow. Should have started using this a while ago.

I thought for sure I could modify your script to do what I want, but I am not picking up this VB stuff quickly... need more examples, I think. Any chance you could modify this script to:

-Only work on the active worksheet
-Only work on the selected chart
-Step through as many series as are present and colorize them all

Any one of these would be helpful. Thanks!

flip-mode · Sat Jun 06, 2009 10:58 am

U2K3, if I was Sylar, I'd be coming after your brain boy! I'd much rather eat a little bit of gray matter than take the time required to learn something. Actually, that isn't true, I find the learning process to be quite thrilling, I just don't have much time. When the kids move out............

TR Forums

A request for Excel programming gurus

A request for Excel programming gurus

Re: A request for Excel programming gurus

Re: A request for Excel programming gurus

Re: A request for Excel programming gurus

Re: A request for Excel programming gurus

Re: A request for Excel programming gurus

Who is online