Thread: Conditional Chart Formatting

Hello,

I need help in conditionally formatting a chart.
I the attached file I need to color Item2's Series red if value is below 40 and green if above

How this can be done?

Thanks for the help

Being a pivot chart you can not use the usual approach of extra series.

Instead you need event code. right click the sheet tab for the chart and pick view code. Paste the following.

Private Sub Chart_Calculate()
    Dim objSeries As Series
    Dim lngPoint As Long
    
    Set objSeries = ActiveChart.SeriesCollection(2)
    For lngPoint = 1 To objSeries.Points.Count
        If Application.WorksheetFunction.Index(objSeries.Values, 1, lngPoint) > 40 Then
            objSeries.Points(lngPoint).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
        Else
            objSeries.Points(lngPoint).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
        End If
    Next

End Sub

Sorry, that was xl2007 code.

Private Sub Chart_Calculate()
    Dim objSeries As Series
    Dim lngPoint As Long
    
    Set objSeries = ActiveChart.SeriesCollection(2)
    For lngPoint = 1 To objSeries.Points.Count
        If Application.WorksheetFunction.Index(objSeries.Values, 1, lngPoint) > 40 Then
            objSeries.Points(lngPoint).Interior.ColorIndex = 14
        Else
            objSeries.Points(lngPoint).Interior.ColorIndex = 3
        End If
    Next

End Sub

You need to provide more information as currently all points in the series will be tested against a single cell.

You need quotes around the cell address.

         If Sheets("Calculations").Range("AI4").Value < 1 Then

Assuming each record is discrete then this should work.

Private Sub Chart_Calculate()

    Dim lngIndex As Long
    Dim strOuterLabel As String
    Dim strInnerLabel As String
    Dim strLabel As String
    Dim strTemp As String
    Dim lngRow As Long
        
    On Error Resume Next
    
    For lngIndex = 1 To ActiveChart.SeriesCollection(1).Points.Count
        strLabel = Application.WorksheetFunction.Index(ActiveChart.SeriesCollection(1).XValues, 1, lngIndex)
        strInnerLabel = Split(strLabel, Chr(10))(0)
        strTemp = Trim(Split(strLabel, Chr(10))(1))
        If Len(strTemp) > 0 Then
            strOuterLabel = strTemp
        End If
        
        If strInnerLabel = "Item 7" Then
            Debug.Print "Check Value ", strInnerLabel, strOuterLabel
            lngRow = Application.WorksheetFunction.Match(strOuterLabel, Sheet1.Range("A1:A12"), 0)
            If Sheet1.Cells(lngRow, 11) = 1 Then
                ActiveChart.SeriesCollection(1).Points(lngIndex).Interior.ColorIndex = 3
            Else
                ActiveChart.SeriesCollection(1).Points(lngIndex).Interior.ColorIndex = 14
            End If
        End If
    Next
    
End Sub

I don't think you can remove those zero items.

It's all or nothing. If you try to hide Item4, for example, all Item4 details will go.

If you restructure your data and don't include the zero records then it will do it automatically.