VBA – Change (or Clear) a Cell’s Background Color

Written by

Mel Jenkins

Reviewed by

Steve Rynearson

Last updated on March 20, 2024

This tutorial will demonstrate how to change a cell’s background color using VBA.

Change Cell Background Color with Interior.colorindex

To change a cell’s background color using VBA you can use the Interior.Colorindex property. Here’s a couple ways to change the background color of cell A1.

An example using the Range() method:

Range("A1").Interior.ColorIndex = 5

An example using the Cells() method:

Cells(1, 1).Interior.ColorIndex = 15

Need an easy way to determine what number equals what color? Check out Color Reference For Colorindex.

This article will demonstrate how to clear the background color of a cell in VBA.

VBA code can change the format of cells, including the background color. You can remove any format, including background color that may have been previously set on a cell.

Clear Cell Color

VBA code can change the format of cells, including the background color. You can remove any format, including background color that may have been previously set on a cell.

To clear a cell’s color, select the cells where you wish to remove the background color.

vba remove cell color

Run the following macro:

Sub RemoveCellColor()
   Selection.Interior.Color = xlNone
End Sub

This is equivalent to selecting No Fill from the Background color drop down in the Ribbon.

 

vba no fill

If you were to record a macro to remove the background color, you may get the following code being created:

Sub RemoveColor()
  With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
  End With
End Sub

This macro would perform the exact same function as the one above, but the one above is more efficient in that it is only one line of code!

If you wish to remove the background color onĀ  a specific range of cells, we can use this code below:

Sub RemoveCellColor()
  Range("B2:F2").Interior.Color = xlNone
End Sub

or, if you wish to use a variable, this code will do the same thing!

Sub RemoveCellColor()
  Dim rng As Range
  Set rng = Range("B2:F2")
  rng.Interior.Color = xlNone
End Sub

vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples