Hi,
I would need your help regarding a worksheet change event.
On Chart worksheet in cell C6 I have drop down list (with material numbers) and when this value change then the MinMaxLabelColoring code (<= working fine alone) should be called and executed, but this is not working.
Could you please help me to figure out the right way?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Worksheets("Chart").Range("$C$6") Then
Call MinMaxLabelColoring
End If
End Sub
Sub MinMaxLabelColoring()
Dim ws As Worksheet
Dim ch As ChartObject
Dim i As Long, j As Long
Dim maxVal As Long, minVal As Long
Dim Ser1Color As Long, Ser2Color As Long
Set ws = Sheets("Evaluation")
maxVal = [MIN(IF(NOT(ISNA(MAX)),MAX))]
minVal = [MIN(IF(NOT(ISNA(MIN)),MIN))]
Sheets("Chart").Activate
Set ch = Sheets("Chart").ChartObjects("Chart1")
Ser1Color = 13998939
Ser2Color = 8355711
With ch.Chart
For i = 1 To 2
For j = 1 To .SeriesCollection(i).Points.Count
Debug.Print .SeriesCollection(i).Points(j).DataLabel.Font.Color
If .SeriesCollection(i).Points(j).DataLabel.Caption = minVal Then
.SeriesCollection(i).Points(j).DataLabel.Font.Color = vbRed
ElseIf .SeriesCollection(i).Points(j).DataLabel.Caption = maxVal Then
.SeriesCollection(i).Points(j).DataLabel.Font.Color = rgbGreen
Else
If i = 1 Then
.SeriesCollection(i).Points(j).DataLabel.Font.Color = Ser1Color
ElseIf i = 2 Then
.SeriesCollection(i).Points(j).DataLabel.Font.Color = Ser2Color
End If
End If
Next j
Next i
End With
End Sub
Thank you in advance the reply!
Bookmarks