Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Conditional Formatting for Charts
Hi all,
I understand the concept of conditional formatting in spreadsheets in XL 2003 just fine. Can the same functionality be extened to XL Charts? I am thinking it has to be a VBA solution, which is not a problem. I just wanted a sanity or insanity check before doing it all with VBA. Looking for evaluating values and formatting differently and finding test strings and formatting them in same color. Best example there is Coke vs. Pepsi. Coke should always be Red and Pepsi should always be Blue no matter where they show up in something like a Share of Market Chart. Coke is highest share overall and therefore the first colorindex(x) but Pepsi, as I recall, has the highest share, or at least used to, in Fountain sales and should always show up as Blue(other colorindex). I know how to do this with Table-based programming lookup table but was wondering if it was already built in. Thank you, Brian Reilly, PowerPoint MVP |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Conditional Formatting for Charts
No sanity check necessary Brian,
No way to do this at present without VBA, we have asked Microsoft for this kind of feature, but who knows.... -- Thanks, Shane Devenshire "Brian Reilly, MVP" wrote: Hi all, I understand the concept of conditional formatting in spreadsheets in XL 2003 just fine. Can the same functionality be extened to XL Charts? I am thinking it has to be a VBA solution, which is not a problem. I just wanted a sanity or insanity check before doing it all with VBA. Looking for evaluating values and formatting differently and finding test strings and formatting them in same color. Best example there is Coke vs. Pepsi. Coke should always be Red and Pepsi should always be Blue no matter where they show up in something like a Share of Market Chart. Coke is highest share overall and therefore the first colorindex(x) but Pepsi, as I recall, has the highest share, or at least used to, in Fountain sales and should always show up as Blue(other colorindex). I know how to do this with Table-based programming lookup table but was wondering if it was already built in. Thank you, Brian Reilly, PowerPoint MVP |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Conditional Formatting for Charts
Hello,
I have a sub that loops through a range based off of a pivot table that will change the color of the chart based on criteria you can assign in VBA. I have pasted the code below if you are interested. It creates a pivot table/chart and then changes the colors based on the variables assigened. Sub test1() Dim x As String, y As Long, Cell As Range, wb As Workbook, ws As Worksheet, strBuyer As String Set wb = ActiveWorkbook Set ws = wb.Sheets(1) strBuyer = wb.Sheets(1).Range("D2").Value Sheets("temp").Range("A1").CurrentRegion.Name = "Data" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Data").CreatePivotTable TableDestination:="", TableName _ :="strBuyer" ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.PivotTables("strBuyer").CalculatedFiel ds.Add "Fill Rate", _ "=ROUND('. Complete Lines' /'. Lines', 2)", True ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("strBuyer").SmallGrid = False ActiveSheet.PivotTables("strBuyer").AddFields RowFields:="Vendor ." ActiveSheet.PivotTables("strBuyer").PivotFields("F ill Rate"). _ Orientation = xlDataField x = ActiveSheet.Name Charts.Add ActiveChart.SetSourceData Source:=Sheets(x).Range("A4").CurrentRegion ActiveChart.Location Whe=xlLocationAsNewSheet y = WorksheetFunction.CountA(Sheets(x).Range("B:B")) - 2 Application.ScreenUpdating = False For Each Cell In Sheets(x).Range("B5").Resize(y) If Cell.Value < 0.95 Then ActiveChart.SeriesCollection(1).Points(Cell.Row - 4).Interior.ColorIndex = 3 ElseIf Cell.Value = 0.95 And Cell.Value < 0.96 Then ActiveChart.SeriesCollection(1).Points(Cell.Row - 4).Interior.ColorIndex = 6 Else ActiveChart.SeriesCollection(1).Points(Cell.Row - 4).Interior.ColorIndex = 4 End If Next Cell Application.ScreenUpdating = True End Sub HTH Thanks, Roger "Brian Reilly, MVP" wrote: Hi all, I understand the concept of conditional formatting in spreadsheets in XL 2003 just fine. Can the same functionality be extened to XL Charts? I am thinking it has to be a VBA solution, which is not a problem. I just wanted a sanity or insanity check before doing it all with VBA. Looking for evaluating values and formatting differently and finding test strings and formatting them in same color. Best example there is Coke vs. Pepsi. Coke should always be Red and Pepsi should always be Blue no matter where they show up in something like a Share of Market Chart. Coke is highest share overall and therefore the first colorindex(x) but Pepsi, as I recall, has the highest share, or at least used to, in Fountain sales and should always show up as Blue(other colorindex). I know how to do this with Table-based programming lookup table but was wondering if it was already built in. Thank you, Brian Reilly, PowerPoint MVP |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Conditional Formatting for Charts
Who said it couldn't be done without VBA?
http://peltiertech.com/Excel/Charts/...nalChart1.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Brian Reilly, MVP" wrote in message ... Hi all, I understand the concept of conditional formatting in spreadsheets in XL 2003 just fine. Can the same functionality be extened to XL Charts? I am thinking it has to be a VBA solution, which is not a problem. I just wanted a sanity or insanity check before doing it all with VBA. Looking for evaluating values and formatting differently and finding test strings and formatting them in same color. Best example there is Coke vs. Pepsi. Coke should always be Red and Pepsi should always be Blue no matter where they show up in something like a Share of Market Chart. Coke is highest share overall and therefore the first colorindex(x) but Pepsi, as I recall, has the highest share, or at least used to, in Fountain sales and should always show up as Blue(other colorindex). I know how to do this with Table-based programming lookup table but was wondering if it was already built in. Thank you, Brian Reilly, PowerPoint MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting in Bar Charts with Labeling | Charts and Charting in Excel | |||
conditional formatting in charts | Charts and Charting in Excel | |||
Conditional formatting backgrounds in charts | Charts and Charting in Excel | |||
Conditional formatting in Charts? | Charts and Charting in Excel |