If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Change colour of bars/lines in charts when data in rows/columns
Hi Everyone,
I'm putting together some vb code to change the colours of the bars or lines on a chart in one go. I've set it up so far for when the data is in rows (ie the individual series lie along the x axis). However, I need to consider how to cycle through the data points for when it is in columns, which essentially means I need to search for xlcategory names and the relavent points (I think). I suppose I also need to consider other types of charts and so would also be grateful for any guidance in how to best do this (as I presume you can't use the 'interior' object for when using lines!). Here's the code I've written which works fine for when data is in rows. Below also is the data I've used. ----------------------- Sub SetChartColoursForMults() Dim Chartobj As Chart Dim s As series Dim pt As Point On Error GoTo error_it Chartname = ActiveChart.Name On Error GoTo 0 Response = MsgBox("Please make sure you have selected the chart to change the series colours." & Chr(10) _ & "The following Multiples will be set to the following [excel] colours:-" & Chr(10) & Chr(10) _ & "Tesco Blue" & Chr(10) _ & "Sainsbury Orange" & Chr(10) _ & "Asda Bright Green" & Chr(10) _ & "Morrisons Black" & Chr(10) _ & "Safeway Red" & Chr(10) _ & "Somerfield Turquoise" & Chr(10) _ & "Kwik Save Pink" & Chr(10) _ & "Waitrose Green" & Chr(10) _ & "Iceland Tan" & Chr(10) _ & "C&I Gold", vbOKCancel, "Set Chart Colours for Multiples") If Response = vbCancel Then Exit Sub With ActiveChart For Each s In .SeriesCollection seriesname = s.Name Select Case seriesname 'for when data is in rows Case "Tesco": NewCol = 5 'Blue or could use vbblue Case "Sainsbury": NewCol = 46 'Orange Case "Asda": NewCol = 4 'Bright Green Case "Morrisons": NewCol = 1 'Black Case "Safeway": NewCol = 3 'Red Case "Somerfield": NewCol = 28 'Turquoise Case "Kwik Save": NewCol = 26 'Pink Case "Waitrose": NewCol = 10 'Green Case "Iceland": NewCol = 40 'Tan Case "C&I": NewCol = 44 'Gold End Select With s.Interior .ColorIndex = NewCol .Pattern = xlSolid End With ' plus need to check for columns when data is in coloumns ' hmmm how do i do this?! Next End With Exit Sub error_it: Select Case Err Case 91 MsgBox "Please select a chart!", vbOKOnly, "Set Chart Colours for Multiples" Case Else MsgBox "There is a problem! Error code: " & Err & Chr(10) & Err.Description End Select End Sub ------------------- Data: Multiples sales '04 sale '05 Tesco 23 34 Sainsbury 34 56 Asda 56 56 Morrisons 68 45 Safeway 65 23 Somerfield 32 34 Kwik Save 34 56 Waitrose 56 68 Iceland 56 65 C&I 45 32 Thanks in advance for your help. STOP PRESS Hiya, Update for you all, I've managed to get a bit further on the code ie testing for xlrows / xlcolumns by using the plotby command. However I'm still stuck on getting the corresponding x axis category label / value for that particular point. Can anyone help? Many thanks RodP |
#2
|
|||
|
|||
Change colour of bars/lines in charts when data in rows/columns
You may have better luck getting answers if you post to the excel.charting
group. http://www.microsoft.com/communities...s/default.aspx has an alpha list of groups. -- Echo [MS PPT MVP] http://www.echosvoice.com "RodP" wrote in message ... Hi Everyone, I'm putting together some vb code to change the colours of the bars or lines on a chart in one go. I've set it up so far for when the data is in rows (ie the individual series lie along the x axis). However, I need to consider how to cycle through the data points for when it is in columns, which essentially means I need to search for xlcategory names and the relavent points (I think). I suppose I also need to consider other types of charts and so would also be grateful for any guidance in how to best do this (as I presume you can't use the 'interior' object for when using lines!). Here's the code I've written which works fine for when data is in rows. Below also is the data I've used. ----------------------- Sub SetChartColoursForMults() Dim Chartobj As Chart Dim s As series Dim pt As Point On Error GoTo error_it Chartname = ActiveChart.Name On Error GoTo 0 Response = MsgBox("Please make sure you have selected the chart to change the series colours." & Chr(10) _ & "The following Multiples will be set to the following [excel] colours:-" & Chr(10) & Chr(10) _ & "Tesco Blue" & Chr(10) _ & "Sainsbury Orange" & Chr(10) _ & "Asda Bright Green" & Chr(10) _ & "Morrisons Black" & Chr(10) _ & "Safeway Red" & Chr(10) _ & "Somerfield Turquoise" & Chr(10) _ & "Kwik Save Pink" & Chr(10) _ & "Waitrose Green" & Chr(10) _ & "Iceland Tan" & Chr(10) _ & "C&I Gold", vbOKCancel, "Set Chart Colours for Multiples") If Response = vbCancel Then Exit Sub With ActiveChart For Each s In .SeriesCollection seriesname = s.Name Select Case seriesname 'for when data is in rows Case "Tesco": NewCol = 5 'Blue or could use vbblue Case "Sainsbury": NewCol = 46 'Orange Case "Asda": NewCol = 4 'Bright Green Case "Morrisons": NewCol = 1 'Black Case "Safeway": NewCol = 3 'Red Case "Somerfield": NewCol = 28 'Turquoise Case "Kwik Save": NewCol = 26 'Pink Case "Waitrose": NewCol = 10 'Green Case "Iceland": NewCol = 40 'Tan Case "C&I": NewCol = 44 'Gold End Select With s.Interior .ColorIndex = NewCol .Pattern = xlSolid End With ' plus need to check for columns when data is in coloumns ' hmmm how do i do this?! Next End With Exit Sub error_it: Select Case Err Case 91 MsgBox "Please select a chart!", vbOKOnly, "Set Chart Colours for Multiples" Case Else MsgBox "There is a problem! Error code: " & Err & Chr(10) & Err.Description End Select End Sub ------------------- Data: Multiples sales '04 sale '05 Tesco 23 34 Sainsbury 34 56 Asda 56 56 Morrisons 68 45 Safeway 65 23 Somerfield 32 34 Kwik Save 34 56 Waitrose 56 68 Iceland 56 65 C&I 45 32 Thanks in advance for your help. STOP PRESS Hiya, Update for you all, I've managed to get a bit further on the code ie testing for xlrows / xlcolumns by using the plotby command. However I'm still stuck on getting the corresponding x axis category label / value for that particular point. Can anyone help? Many thanks RodP |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Pulling data from 1 sheet to another | Dave1155 | Worksheet Functions | 1 | January 12th, 2005 05:55 PM |
Format on data to import to Access tables? (I need your advice) | Niklas Östergren | General Discussion | 5 | December 13th, 2004 02:54 PM |
Is this possible with Excel Chart? | q582gmzhi | Charts and Charting | 1 | September 8th, 2004 03:33 AM |
SQL view of messed up action queries | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 09:53 PM |
How to create graphs in a monthly report where the base data can change | John Clarke | Charts and Charting | 3 | June 25th, 2004 02:22 AM |