XY Scatter Chart - Flag Duplicates
A post sometime ago to the Microsoft Excel Charting Discussion Group asks . . . how can I automatically flag duplicate values on an XY scatter chart with a different colored point? My thought was to write a procedure that identified the duplicates via a helper column in the worksheet, accessed the chart, and then recolored the duplicate points. Assuming an XY scatter chart based on the values in the first and second columns, I added a third helper column. The helper column concatenates the X and Y values.
I renamed the chart "Cht1". Assuming a single X and Y series, the code below flags the duplicates. In the example, the marker size for each duplicated point is increased to 10 and the color of the marker is switched from blue to green.
Sub FlayXYDups()
On Error Resume Next
Dim Rng As Range
Dim Cell As Range
Dim Cnt As Integer
Dim UniqueValues As New Collection
Dim ErrColor As Integer
Dim Cht As ChartObject
Set Cht = ActiveSheet.ChartObjects("Cht1")
ErrColor = 10
Cnt = 0
For Each Rng In Range("D3:D20")
Cnt = Cnt + 1
UniqueValues.Add Rng.Value, CStr(Rng.Value)
If Err.Number = 457 Then
With Cht.Chart.SeriesCollection(1).Points(Cnt)
.MarkerSize = 10
.MarkerForegroundColorIndex = ErrColor
.MarkerBackgroundColorIndex = ErrColor
End With
Rng.Interior.ColorIndex = ErrColor
End If
Err.Number = 0
Next Rng
End Sub
The chart showing duplicated values looks like this:
The procedure also flags the duplicates on the spreadsheet.
