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.

 

[quicknote]

I understand the importance of automating tasks to save time and increase efficiency. It sounds like you are on the right track in using VBA and macros to achieve your goal.

To answer your question, yes, there is a way to obtain the information about which data row is plotted against the top row from the Chart object. You can use the ".SeriesCollection" property to access the individual data series in your chart. From there, you can use the ".Format.Line" property to change the color of the line based on the numerical value of each data point.

I would also recommend using a loop to go through each worksheet and apply the macro to all the graphs. This way, you don't have to manually run the macro for each individual sheet.

I hope this helps and good luck with your project!