Conditional Datalabel formating
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 |
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).DataLabe l.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).DataLabe l.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).DataLabe l.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).DataLabe l.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).DataLabe l.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).DataLabe l.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).DataLabe l.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).DataLabe l.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).DataLabe l.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).DataLabe l.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).DataLabe l.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).DataLabe l.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).DataLabe l.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).DataLabe l.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).DataLabe l.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).DataLabe l.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 |
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/...nalChart1.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/ _______ Karl wrote: 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 |
All times are GMT +1. The time now is 11:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com