[Excel VBA] Change Scatter Plot Line Color Using a Color Palette Button

Background

When working with multiple graphs in Excel, I often encountered a situation where I needed to “change the line color for a specific data series only.”

Doing this manually with the mouse becomes very inefficient as the number of graphs increases. Therefore, I created a VBA solution that allows you to change the line color of a series specified in a cell using a color palette, all with a single button click.

Specifications

• Sheet1 contains multiple scatter plots.
• Cell A1 contains the name of the series you want to change (e.g., “NO1”, “NO2”).
• When you click the “Change Color” button (Shape), a color palette appears.
• The selected color is applied to the lines of the corresponding series.
• The border color of the button (shape) also changes to the selected color.

Prerequisites

• This method uses the Windows API to display the color palette.
• Please place a shape (to act as the button) on the sheet beforehand.

Implementation Code (VBA)

Paste the following code into a Standard Module.

1. Define Windows API and Color Dialog

Private Type CHOOSECOLOR
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    rgbResult As Long
    lpCustColors As String
    Flags As Long
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Private Declare Function ChooseColor Lib "comdlg32.dll" Alias "ChooseColorA" (pChoosecolor As CHOOSECOLOR) As Long
Private Declare Function OleTranslateColor Lib "oleaut32.dll" (ByVal lOleColor As Long, ByVal lpPal As Long, lpColorRef As Long) As Long

Const CC_RGBINIT = &H1&
Const CC_FULLOPEN = &H2&

Private Function ShowColorDialog(Optional ByVal DefaultColor As Long = 0) As Long
    Dim cc As CHOOSECOLOR
    Dim CustColors As String

    CustColors = String$(16 * 4, 0)
    With cc
        .lStructSize = Len(cc)
        .hwndOwner = Application.hwnd
        .Flags = CC_RGBINIT Or CC_FULLOPEN
        .rgbResult = DefaultColor
        .lpCustColors = CustColors
    End With

    If ChooseColor(cc) Then
        ShowColorDialog = cc.rgbResult
    Else
        ShowColorDialog = -1
    End If
End Function

2. Logic to Select Color and Update Series

Sub Change_the_ColorLine()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim ser As Series
    Dim targetName As String
    Dim selectedColor As Long
    Dim shp As Shape

    Set ws = ThisWorkbook.Sheets("Sheet1")
    targetName = ws.Range("A1").Value

    ' Open Color Palette
    selectedColor = ShowColorDialog
    If selectedColor = -1 Then Exit Sub

    ' Change line color for the target series in each chart
    For Each chartObj In ws.ChartObjects
        For Each ser In chartObj.Chart.FullSeriesCollection
            If ser.Name = targetName Then
                ser.Format.Line.ForeColor.RGB = selectedColor
            End If
        Next ser
    Next chartObj

    ' Change the border color of the button itself
    Set shp = ActiveSheet.Shapes(Application.Caller)
    shp.Line.ForeColor.RGB = selectedColor
End Sub

How to Register the Macro

1. Insert a shape in Excel (e.g., a rectangle with the text “Change Color”).
2. Right-click the shape -> Assign Macro.
3. Select Change_the_ColorLine and click OK.

Now, when you click the shape, the color palette will appear. Select a color, and it will be applied to the graph lines.

Important Notes

• Run via the Shape: This code uses Application.Caller, so it must be triggered by clicking the shape. Running it directly from the VBE will cause an error.
• Series Name: Ensure the series name in Cell A1 exactly matches the series name in the chart.
• Windows Only: This code uses the Windows API (comdlg32.dll), so it will not work on Mac.

Summary

• You can use ShowColorDialog to retrieve a color selected by the user.
• By looping through charts, you can pinpoint and color specific data series.
• Changing the button’s color simultaneously creates a more intuitive and visual user interface.

This is a very useful technique for anyone looking to automate and streamline graph operations in Excel.