Conditional Formatting for Charts

[Brian Reilly, MVP]

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

 

[ShaneDevenshire]

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....

 

[Roger Converse]

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:=xlDatabase, SourceData:= _
"Data").CreatePivotTable TableDestination:="", TableName _
:="strBuyer"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.PivotTables("strBuyer").CalculatedFields.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("Fill Rate"). _
Orientation = xlDataField
x = ActiveSheet.Name
Charts.Add
ActiveChart.SetSourceData Source:=Sheets(x).Range("A4").CurrentRegion
ActiveChart.Location Where:=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

 

[Jon Peltier]

Who said it couldn't be done without VBA?

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

- Jon