[Excel VBA] 3 Ways to Change Shape Fill Color | Mastering RGB and Theme Colors

Introduction

When automatically creating documents in Excel VBA, have you ever wanted to change the colors of shapes or charts based on specific conditions, or ensure a unified design across your entire report?

VBA allows you to set the fill color of shapes freely and flexibly. There are several ways to specify colors, and choosing the right one for your purpose is key to efficiency.

In this article, I will explain three representative methods for specifying shape fill colors in VBA, along with clear code examples.

Prerequisite: Name the Target Shape

To ensure we can control the specific shape with VBA, let’s name the shape “ColorSettingSample” in advance. Naming your shapes clarifies the intent of the code and makes management easier.

(How to name a shape: Select the shape → “Shape Format” tab → “Selection Pane” → Rename the object)

Method 1: Specify Color Directly by RGB Value (.RGB)

The most reliable way to express a specific color accurately is by using RGB values. RGB expresses color by mixing the three primary colors of light: Red, Green, and Blue, using values from 0 to 255 for each.

VBA Code Example

Sub SetColor_By_RGB()
    ' Target the shape named "ColorSettingSample"
    ' RGB value (0, 128, 128) creates Teal Green
    ActiveSheet.Shapes("ColorSettingSample").Fill.ForeColor.RGB = RGB(0, 128, 128)
End Sub

Explanation Assign the value created by the RGB(Red, Green, Blue) function to the Fill.ForeColor.RGB property. This is very convenient when you have a specific color code you want to use, such as a web color code or a company logo color.

Method 2: Specify by Theme Color Index (.SchemeColor)

This method links the color to Excel’s “Theme Colors” palette. This ensures that if you later change the theme from the Page Layout tab in Excel, the color of the shape will automatically adjust to match the new theme.

VBA Code Example

Sub SetColor_By_SchemeColor()
    ' Target the shape named "ColorSettingSample"
    ' Specify Theme Color Index 10 (Usually a blue accent color)
    ActiveSheet.Shapes("ColorSettingSample").Fill.ForeColor.SchemeColor = 10
End Sub

Explanation Specify an index number (an integer starting from 1) from the theme color palette to the Fill.ForeColor.SchemeColor property. Which number corresponds to which color depends on the current theme, but generally, 8 to 13 are assigned to accent colors. This is ideal when you want to maintain a unified design throughout the workbook.

Method 3: Specify by Theme Color and Brightness (.ObjectThemeColor + .TintAndShade)

This is a more flexible version of Method 2. You select a base theme color and then make it lighter (Tint) or darker (Shade) to express more detailed nuances.

VBA Code Example

Sub SetColor_By_ThemeColor_Tint()
    ' Target the shape named "ColorSettingSample"
    With ActiveSheet.Shapes("ColorSettingSample").Fill.ForeColor
        ' Set the base color to "Accent 2" (Usually an orange tone)
        .ObjectThemeColor = msoThemeColorAccent2
        
        ' Darken the base color by 30% (Specify range from -1 to 1)
        .TintAndShade = -0.3
    End With
End Sub

Explanation First, select a base color using constants like msoThemeColorAccent1 or msoThemeColorBackground1 with the ObjectThemeColor property. Next, adjust the brightness using the .TintAndShade property. The value ranges from -1 (darkest) to 1 (lightest), with 0 being the original base color. In this example, -0.3 results in a slightly darker shade.

Which Method Should You Use?

If you are unsure which method to choose, use the following guide:

• RGB (.RGB)
  • Use when: You want to use a specific color that must never change, such as a company logo color.
  • Feature: Reproduces the exact same color regardless of the user’s Excel theme settings.
• Theme Colors (.SchemeColor / .ObjectThemeColor)
  • Use when: You want to change colors according to the workbook’s design theme or unify color tones across multiple reports.
  • Feature: High maintainability. You can change all colors at once simply by changing the Excel theme without modifying the VBA code.

Summary

In this article, I introduced three main methods for setting shape fill colors in VBA:

1. .RGB: Pinpoint color specification.
2. .SchemeColor: Specification by theme color palette number.
3. .ObjectThemeColor & .TintAndShade: Adjustment of brightness based on a theme color.

By using these methods appropriately, you can create Excel macros that are not only visually appealing but also easy to maintain. Please use these techniques for your business automation.