Conditional Datalabel formating

[Karl]

Hello,
We have a Excel 2003 worksheet named 'OBQI 2004' that has multiple
embedded column charts.

Here is the source data for one of the charts:

F G H

85 (Prior) (Current)
86 MO700 32% 32%
87 MO690 40% 41%
88 MO420 53% 52%
89 No Prior Data 37%
90 MO670 56% 55%
91 MO780 34% 33%
92 No Prior Data 50%
93 No Prior Data 57%

So the data range value is: ='OBQI 2004'!$F$85:$H$93

Each chart has two series:

Series 1
Name: ='OBQI 2004'!$G$85
Values: ='OBQI 2004'!$G$86:$G$93

Series 2
Name: ='OBQI 2004'!$H$85
Values: ='OBQI 2004'!$H$86:$H$93

For each chart the datalabels for series 1 has been removed.

The datalabels for series 2 has been positioned at the bottom of each
column.

ok... here's the question.

I need to format each series 2 datalabel to have a white background and
red font if the value of Column H(Current) - Column G(Prior) is less
than 0. Otherwise, the datalabel is formated with a green background
with a black font.

This is currently a manual process that I would like to automate.

Can this be done in VBA? Can someone please provide me with or point me
to an example?

TIA

Karl

 

[Karl]

Ok.... Here's code to do 1 chart.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/20/2005
'
' Keyboard Shortcut: Ctrl+z
'
' Label 1

Application.ScreenUpdating = False
Windows("Book1").Activate
Sheets("Sheet1").Select
If ActiveSheet.Range("H86").Value - ActiveSheet.Range("G86").Value < 0
Then
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Points(1).DataLabel.Select
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 3
End With
ElseIf ActiveSheet.Range("H86").Value -
ActiveSheet.Range("G86").Value >= 0 Then
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Points(1).DataLabel.Select
With Selection.Interior
.ColorIndex = 1
.PatternColorIndex = 2
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 4
End With
End If

' Label 2

Sheets("Sheet1").Select
If ActiveSheet.Range("H87").Value - ActiveSheet.Range("G87").Value < 0
Then
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Points(2).DataLabel.Select
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 3
End With
ElseIf ActiveSheet.Range("H87").Value -
ActiveSheet.Range("G87").Value >= 0 Then
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Points(2).DataLabel.Select
With Selection.Interior
.ColorIndex = 1
.PatternColorIndex = 2
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 4
End With
End If

' Label 3

Sheets("Sheet1").Select
If ActiveSheet.Range("H88").Value - ActiveSheet.Range("G88").Value < 0
Then
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Points(3).DataLabel.Select
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 3
End With
ElseIf ActiveSheet.Range("H88").Value -
ActiveSheet.Range("G88").Value >= 0 Then
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Points(3).DataLabel.Select
With Selection.Interior
.ColorIndex = 1
.PatternColorIndex = 2
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 4
End With
End If

' Label 4

Sheets("Sheet1").Select
If ActiveSheet.Range("H89").Value - ActiveSheet.Range("G89").Value < 0
Then
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Points(4).DataLabel.Select
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 3
End With
ElseIf ActiveSheet.Range("H89").Value -
ActiveSheet.Range("G89").Value >= 0 Then
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Points(4).DataLabel.Select
With Selection.Interior
.ColorIndex = 1
.PatternColorIndex = 2
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 4
End With
End If

' Label 5

Sheets("Sheet1").Select
If ActiveSheet.Range("H90").Value - ActiveSheet.Range("G90").Value < 0
Then
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Points(5).DataLabel.Select
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 3
End With
ElseIf ActiveSheet.Range("H90").Value -
ActiveSheet.Range("G90").Value >= 0 Then
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Points(5).DataLabel.Select
With Selection.Interior
.ColorIndex = 1
.PatternColorIndex = 2
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 4
End With
End If

' Label 6

Sheets("Sheet1").Select
If ActiveSheet.Range("H91").Value - ActiveSheet.Range("G91").Value < 0
Then
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Points(6).DataLabel.Select
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 3
End With
ElseIf ActiveSheet.Range("H91").Value -
ActiveSheet.Range("G91").Value >= 0 Then
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Points(6).DataLabel.Select
With Selection.Interior
.ColorIndex = 1
.PatternColorIndex = 2
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 4
End With
End If

' Label 7

Sheets("Sheet1").Select
If ActiveSheet.Range("H92").Value - ActiveSheet.Range("G92").Value < 0
Then
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Points(7).DataLabel.Select
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 3
End With
ElseIf ActiveSheet.Range("H92").Value -
ActiveSheet.Range("G92").Value >= 0 Then
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Points(7).DataLabel.Select
With Selection.Interior
.ColorIndex = 1
.PatternColorIndex = 2
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 4
End With
End If

' Label 8

Sheets("Sheet1").Select
If ActiveSheet.Range("H93").Value - ActiveSheet.Range("G93").Value < 0
Then
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Points(8).DataLabel.Select
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 3
End With
ElseIf ActiveSheet.Range("H93").Value -
ActiveSheet.Range("G93").Value >= 0 Then
ActiveSheet.ChartObjects("Chart 12").Activate
ActiveChart.SeriesCollection(1).Points(8).DataLabel.Select
With Selection.Interior
.ColorIndex = 1
.PatternColorIndex = 2
.Pattern = xlSolid
End With
With Selection.Font
.ColorIndex = 4
End With
End If
Application.ScreenUpdating = True
End Sub

Any help streamlining this would be greatly appreciated because I have
11 other charts embedded on the same worksheet that I will need to also
update .

Thanks,

Karl

 

[Jon Peltier]

I have a codeless alternative. Use two dummy series, one for positive
data labels, one for negative. Make these XY series, so you can control
their position precisely where you want the labels; both series have
points in exactly the same locations. Hide the series by formatting them
with no markers and no lines. Along with the data for the two series,
you have data for two set of labels. Use formulas, so one set appears
when the value is >= zero, and the other set appears when the value is <
zero. use a third party add-in like:

Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com/ss

to apply the positive labels to one dummy series and the negatives to
the other. These add-ins link to the labels by formulas, so when the
cells show and hide values, so do the labels. Format the positive labels
with the green scheme, and the negatives with the red.

This is very similar to the conditional charting examples on my web page:

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

I've even used it to help position some labels above the points and
others below, to avoid having to move them manually. It's fully
automatic, without having to run a macro, or rely on one to run after
some event.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______