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
|
|||
|
|||
Chart - Conditional formatting
Hi Everyone,
In order to change conditionally the colours of my data series in a Chart (based on the colour set in a range named "pallet"), I used the following code. The thing is that I need to work in a Clustered Column chart and that the colour conditionality should be based on the Category label, not on the Serie. Example : Category Serie Flowers 5% Clothes 2% Food 15% etc. I would like to select the colour according to the name of the category (ie Flowers = yellow, Clothes = blue, etc). Actually, the routine uses the serie values, as shown in line 4 (DataLabel) of the code. How should I modify this routine to see it work properly with the category name displayed on the X Axis ? Sub SeriesColours() Sheets("Sheet1").ChartObjects(1).Activate For Each ppt In ActiveChart.SeriesCollection(1).Points labl = ppt.DataLabel.Caption Sheets("Sheet1").Range("pallet").Select Selection.Find(What:=labl, After:=ActiveCell).Activate scolor = ActiveCell.Interior.ColorIndex Sheets("Sheet1").ChartObjects("Chart 1").Activate ppt.Interior.ColorIndex = scolor Next Sheets("Sheet1").Cells(1).Select End Sub TIA for your cooperation |
#2
|
|||
|
|||
You need to match the category name, so do it like this:
With Sheets("Sheet1").ChartObjects(1).Chart.SeriesColle ction(1) For iPoint = 1 To .Points.Count sCategory = WorksheetFunction.Index(.XValues, iPoint) ' now find the category in the lookup table You should use something besides ppt for your point variable, since it will remind many other folks of PowerPoint. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Acheron wrote: Hi Everyone, In order to change conditionally the colours of my data series in a Chart (based on the colour set in a range named "pallet"), I used the following code. The thing is that I need to work in a Clustered Column chart and that the colour conditionality should be based on the Category label, not on the Serie. Example : Category Serie Flowers 5% Clothes 2% Food 15% etc. I would like to select the colour according to the name of the category (ie Flowers = yellow, Clothes = blue, etc). Actually, the routine uses the serie values, as shown in line 4 (DataLabel) of the code. How should I modify this routine to see it work properly with the category name displayed on the X Axis ? Sub SeriesColours() Sheets("Sheet1").ChartObjects(1).Activate For Each ppt In ActiveChart.SeriesCollection(1).Points labl = ppt.DataLabel.Caption Sheets("Sheet1").Range("pallet").Select Selection.Find(What:=labl, After:=ActiveCell).Activate scolor = ActiveCell.Interior.ColorIndex Sheets("Sheet1").ChartObjects("Chart 1").Activate ppt.Interior.ColorIndex = scolor Next Sheets("Sheet1").Cells(1).Select End Sub TIA for your cooperation |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
conditional formatting with data labels? | Dave R. | Charts and Charting | 2 | September 20th, 2004 03:57 PM |
Greater than formulas with conditional formatting | Jamie | New Users | 1 | May 21st, 2004 04:32 AM |
Conditional Formatting Questions | Andy B | Worksheet Functions | 1 | May 12th, 2004 02:48 PM |
Conditional Formatting on a cell that has an IF function | DDM | Worksheet Functions | 0 | May 11th, 2004 05:13 PM |
pivot chart formatting? | hugh jass | Worksheet Functions | 1 | September 17th, 2003 05:02 AM |