# Thread: Find maximum in chart range

1. ## Find maximum in chart range

Hi guys,

I have a question and i've working on it for 2 weeks now.

I have a chart with some graphs on it. in any of the line i need to mark the initial point and the final point then the program will mark the maximum in this range

Our chart in some point will become like (almost) and in this range, i need to get the maximum...

Code:
```Sub Ponto1()
Set x1 = Selection
With x1
.MarkerStyle = -4168
.MarkerSize = 5
.Format.Line.Visible = msoTrue
.Format.Line.ForeColor.RGB = RGB(255, 0, 0)
.Format.Line.Transparency = 0
.Format.Line.Weight = 0.75
End With
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
'ws.Range("G4").Value = x1
'ws.Range("G4").Value = Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
ws.Range("G4").Value = Application.Transpose(x1.Values)
End Sub
Sub Ponto2()

Set x2 = Selection
With x2

.MarkerStyle = -4168
.MarkerSize = 5
.Format.Line.Visible = msoTrue
.Format.Line.ForeColor.RGB = RGB(255, 0, 0)
.Format.Line.Transparency = 0
.Format.Line.Weight = 0.75
End With
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("H4").Value = x2
'Call calcula_Max
End Sub
Sub calcula_Max()

Dim xR As Object
Set xR = x1

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("G4").Value = x1
ws.Range("H4").Value = x2

With xR
.MarkerStyle = -4168
.MarkerSize = 5
.Format.Line.Visible = msoTrue
.Format.Line.ForeColor.RGB = RGB(255, 0, 0)
.Format.Line.Transparency = 0
.Format.Line.Weight = 0.75
.ApplyDataLabels
.DataLabel.NumberFormat = "0"
End With

x1.MarkerStyle = -4142
x2.MarkerStyle = -4142

'Set x1 = ""
'Set x2 = ""

End Sub```
it is incomplete because i could't find the first and the last value (x1 and x2) to find the hiest y in this range

2. Dear Ric:

Welcome to the lounge. Perhaps the following article can assist. In effect, you have excel find the Maximum, set this as its own chart value and make it part of the chart as a "point" rather than a line. If you needed to have as VBA, turn on the Macro Recorder, go throught the steps and review the Macro VBA lines to determine what your missing in the above code.
The article can be found at Chandoo.org

3. It did not work, i'm stil stucked

4. You can setup a duplicate series in your chart with column of data that only had the max point.

ie: with original data in A1:A100, a formula in column such as =if(a1=max(\$A\$1:\$A\$100),a1,na()) will capture the max value, then plot the data as a line (just turn off the line and only show the points, I usually make the marker a circle with no fill, marker line color red, and marker size large enough to have the circle larger than the markers in the original dataset that I'm trying to flag)

From here, it's a simple step to modify the formula for the top 10 (for example) values .........

5. ric.de.abreu,

Here is some code that will allow the user to select two points (successively) on the graph then it will format the points, change the line color between the two points, and then marks the highest and lowest point.

The code builds on Zeno's suggestion of Max and Min columns as additional series but performs the calculations in the code then dynamically adjusts the ranges of these functions to match the selected points.

HTH,
Maud

Before points selected:
graph1.png

After points selected:
graph2.png

Code:
```Private PT1, PT2, State As Integer
Private Sub Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
'--------------------------------------
'SET AND DECLARE VARIABLES
Dim I As Integer
'--------------------------------------
'TEST IF ONE MARKER IS SELECTED
If ElementID <> 3 Then 'IS MARKER?
State = 0
PT1 = 0
PT2 = 0
Exit Sub
End If
If Arg2 = -1 Then 'IS SELECTION > 1
State = 0
PT1 = 0
PT2 = 0
Exit Sub
End If
'--------------------------------------
'DETECT THE CURRENT STEP IN THE PROCESS
Select Case State
Case 0 'CLEAR AND POPULATE POINT1
PT1 = Arg2 'REMEMBER FIRST SELECTED POINT
Worksheets("Data").Range("F2:G100").ClearContents 'CLEAR MIN/MAX FORMULAS
Application.EnableEvents = False
'--------------------------------------
'RESET CHART SERIES (MARKER STYLE,SIZE,FILL AND LINE COLOR)
ActiveChart.FullSeriesCollection(1).Select
Selection.MarkerStyle = -4142
Selection.MarkerSize = 5
Selection.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent1
Selection.Format.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent1
'--------------------------------------
'FORMAT FIRST SELECTED POINT
ActiveChart.FullSeriesCollection(1).Points(PT1).Select
Application.EnableEvents = True
With Selection
.MarkerStyle = 8
.MarkerSize = 10
Selection.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent1
End With
State = 1
Exit Sub
'--------------------------------------
'FORMAT SECOND SELECTED POINT
Case 1 'POPULATE POINT2 AND FIND MAX
PT2 = Arg2
With Selection
.MarkerStyle = 8
.MarkerSize = 10
Selection.Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent1
End With
State = 0
Application.EnableEvents = False
'--------------------------------------
'FORMAT GRAPH LINE BETWEEN SELECTED POINTS
For I = PT1 + 1 To PT2
ActiveChart.FullSeriesCollection(1).Points(I).Select
Selection.Format.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent2
Next I
'--------------------------------------
'ADD MIN/MAX FORMULAS TO DATA SHEET COLUMNS F AND G
With Worksheets("Data")
For I = 2 To 14
If I > PT1 And I <= PT2 + 1 Then
If .Cells(I, "E") = WorksheetFunction.Max(.Range("\$E" & PT1 + 1 & ":E\$" & PT2 + 1)) Then
.Cells(I, "F") = .Cells(I, "E")
Else:
.Cells(I, "F") = "#N/A"
End If
Else:
.Cells(I, "F") = "#N/A"
End If
Next I
For I = 2 To 14
If I > PT1 And I <= PT2 + 1 Then
If .Cells(I, "E") = WorksheetFunction.Min(.Range("\$E" & PT1 + 1 & ":E\$" & PT2 + 1)) Then
.Cells(I, "G") = .Cells(I, "E")
Else:
.Cells(I, "G") = "#N/A"
End If
Else:
.Cells(I, "G") = "#N/A"
End If
Next I
End With
Application.EnableEvents = True
End Select
End Sub```

6. For those who may be interested, The code was developed in 2013 but to make it backwards compatible with 2010 or less, change all instances of FullSeriesCollection to just SeriesCollection.

Maud

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•