How Can I Automate Line Color Changes in Excel Graphs Using VBA?

[juzbe]

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!

 

[CFDFEAGURU]

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.