Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Any VBA / Excel gurus present?

  1. Jul 13, 2009 #1
    I'm fairly new to Visual Basic, so the answer to this might be really simple...

    So here's the deal:

    I have something like 500 Excel worksheets, that each contain the following:

    1. 2-N equally long rows of data
    2. A Line graph of one of rows 2-N versus the top row

    What I need to do is change the line color in the graph, according to the numerical value of each data point. Needless to say, since there are hundreds of these graphs, I'd like to be able to write a macro that will do this for me.

    My problem is, the data row that is plotted against the top row varies from sheet to sheet, which means I don't know which row to look at when deciding the color. Is there any way to obtain this information from the Chart object?

    Any help would really be appreciated, I'm starting to think I could've done this manually in the time it's taken me to write the macro :)

    Thanks in advance!
  2. jcsd
  3. Jul 14, 2009 #2
    First, assuming all 500 worksheets are in one workbook, you should re-organize your data to minimize the number of sheets. Upon re-organization have the data set-up in a consistent format. Then use the macro recorder to record the macro that you can use over and over again. The problem with using a macro with a graph is that you will have to change the VBA code for each graph since the data is located in different rows for each graph. Hope that helps.
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook