?
Solved

Highlight the max value of the Data Labels in all of the Excel Charts

Posted on 2014-04-20
2
Medium Priority
?
540 Views
Last Modified: 2014-04-21
Dear Experts:

below macro deletes all data labels featuring 0% values from all the charts of the active worksheet. The macro works just fine. Could somebody help me re-write this code so that the additonal following requirements are met.

I would like to change the font color (to white) of the data labels in all Excel charts of the current worksheet to highlight the maximum values.

There is a tutorial on this on the web by:

http://peltiertech.com/WordPress/highlight-specific-data-label-in-excel-chart/

But I was not able to adjust that sample code to accommodate my requirements.

The code somebody will come up with hopefully can be a stand-alone code of course, it does not need to be incorporated in the code below.

Help is very much appreciated.  Thank you very much in advance.

Regards, andreas

Sub CleanUpChartLabels()
    Dim iPts As Integer
    Dim nPts As Integer
    Dim aVals As Variant
    Dim srs As Series
    Dim myChtObj As ChartObject
   
If msgbox("Would you like to delete 0% datalabels? ... " & vbCrLf & _
"... i.e. all Data labels featuring just 0% of the total count of " & ActiveSheet.ChartObjects.Count & " Charts will be deleted?", vbQuestion + vbYesNo, "0% Datalabels") = vbNo Then
Exit Sub
End If
 
For Each myChtObj In ActiveSheet.ChartObjects
  
   With myChtObj.Chart
        For Each srs In .SeriesCollection
            With srs
                If .HasDataLabels Then
                    nPts = .Points.Count
                   ' i = i + 1
                    aVals = .Values
                        For iPts = 1 To nPts
                            If aVals(iPts) = 0 Then
                                .Points(iPts).HasDataLabel = False
                            End If
                        Next
                End If
            End With
        Next
   End With
Next

End Sub

Open in new window

0
Comment
Question by:AndreasHermle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 23

Accepted Solution

by:
Ejgil Hedegaard earned 2000 total points
ID: 40011798
The Peltier code adjusted to highlight white, and handle all series in the charts.
As stand-alone.

Option Explicit

Sub HighlightMaxDataLabel()
    Dim srs As Series
    Dim vY As Variant
    Dim iPt As Long, nPts As Long
    Dim dMax As Double
    Dim iHighlightColor As Long
    Dim myChtObj As ChartObject

    ' highlight color: change to suit
    iHighlightColor = RGB(255, 255, 255)    'white

    For Each myChtObj In ActiveSheet.ChartObjects
        For Each srs In myChtObj.Chart.SeriesCollection
            
            ' reset all labels to original font color
            With srs.DataLabels.Font
                .Color = .Color
            End With
        
            vY = srs.Values
            nPts = srs.Points.Count

            ' find maximum value
            dMax = vY(1)
            For iPt = 2 To nPts
                If dMax < vY(iPt) Then
                    dMax = vY(iPt)
                End If
            Next iPt
    
            For iPt = 1 To nPts
                ' highlight all labels at maximum value
                If vY(iPt) = dMax Then
                    srs.Points(iPt).DataLabel.Font.Color = iHighlightColor
                End If
            Next iPt
        Next srs
    Next myChtObj
End Sub

Open in new window

0
 

Author Closing Comment

by:AndreasHermle
ID: 40013261
Hi hgholt:

great this did the trick. Thank you very much for  your great and swift support.

Regards, Andreas
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question