Skip to content

Apply Conditional Formatting to an Excel Chart Using VBA Macro

    This video demonstrates how to set up and write a VBA macro that will automatically apply conditional formatting to a chart, target vs sales.  Anything under the target is formatted in red, equal and over in blue.  The macro is stored in the sheet object and runs on the sheet’s change event so that every time the data is updated the macro runs.  A copy of the VBA code can be found beneath the video.

    Private Sub Worksheet_Change(ByVal Target As Range)

Dim t As Long
Dim ch As Chart
Dim s As Series
Dim i As Byte

t = Range("E1")
Set ch = ActiveSheet.ChartObjects(1).Chart
Set s = ch.SeriesCollection("Units Sold")

For i = 1 To s.Points.Count
    If s.Values(i) >= t Then s.Points(i).Interior.Color = RGB(51, 102, 204)
    If s.Values(i) < t Then s.Points(i).Interior.Color = RGB(255, 0, 0)

Next i

End Sub

     

    Tags: