Skip to content

VBA to Conditionally Format Top & Bottom Values in Excel Chart

    This video demonstrates how to write the VBA code needed to conditionally format the top and bottom values displayed in an Excel Chart.  The code featured in the video can be found below.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim ch As Chart
    Dim s As Series
    Dim i As Byte
    
    Set ch = ActiveSheet.ChartObjects(1).Chart
    Set s = ch.SeriesCollection("Units Sold")
    
    s.Interior.Color = RGB(165, 165, 165)
    
    For i = 1 To s.Points.Count
    
        If s.Values(i) = Application.WorksheetFunction.Max(s.Values) _
        Then s.Points(i).Interior.Color = RGB(51, 102, 204)
        If s.Values(i) = Application.WorksheetFunction.Min(s.Values) _
        Then s.Points(i).Interior.Color = RGB(255, 0, 0)
    
    Next i
    
    
    End Sub