Macro for chart formating

QWERTY_Warrior · #1 06-29-2011, 07:38 AM

Im tryng to write a macro that always applies the same formatting to the series in my chart, based on what kind of data the series is. For instance, if the series is “Actual” data it should be represented by blue bars with a frame around, if the series is “Year to go” data it should be represented by red bars with a frame around. My macro so far looks like this:

Sub Ändra_Bars_Lines()
Räknare = 0
Diagramdata = ThisWorkbook.Sheets("DATA_CHART").Range("AI1")
For Each Cell In Range(Diagramdata)
Räknare = Räknare + 1
Select Case Cell.Offset(0, -4)

Case "ACT"
Worksheets("CHART").ChartObjects("Chart 1049").Chart.SeriesCollection(Räknare).Interior.Co lor = RGB(0, 0, 255)
Worksheets("CHART").ChartObjects("Chart 1049").Chart.SeriesCollection(Räknare).Border.Line Style = xlDash

Case "YTG"
Worksheets("CHART").ChartObjects("Chart 1049").Chart.SeriesCollection(Räknare).Interior.Co lor = RGB(255, 0, 0)
Worksheets("CHART").ChartObjects("Chart 1049").Chart.SeriesCollection(Räknare).Border.Line Style = xlDash

Case "PRO"

Worksheets("CHART").ChartObjects("Chart 1049").Chart.SeriesCollection(Räknare).Interior.Co lor = RGB(0, 255, 0)

Case "BUD"
Worksheets("CHART").ChartObjects("Chart 1049").Chart.SeriesCollection(Räknare).Interior.Co lor = RGB(0, 0, 0)
End Select
Next
End Sub

Now this is all good. What I also want to do (but don’t know how) is:

- Add shadows to the “ACT” and “YTG” bars (equivalent of doing: Format data series – Shadow – Presets – Offset diagonal bottom right)
- Make the “PRO” bar a line, with a shadow, instead of a bar (equivalent of doing: Change series chart type – Line, Format data series – Shadow – Presets – Offset diagonal bottom right)
- Make the “BUD” bar a dotted line instead of a bar (equivalent of doing: Change series chart type – Line, Format data series – Linestyle – Dash type – Long dash)

When I use the macro recorder it does not record any of those actions. I have tried searching the net for these variables but to no avail.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro: chart from excel into PP as objects	Jazz43	PowerPoint	0	03-05-2011 09:06 AM
After formating PC	milkmile	Outlook	2	10-12-2010 06:58 AM
Converting manual chart to bar chart?	aligahk06	Excel	0	07-03-2010 12:23 PM
Removing All Formating	basscarp	Word	0	01-31-2010 05:49 PM
Help with a macro to shade a selected pie chart	hzrvkt	Excel	0	02-24-2006 01:25 PM