VBA – Change (or Clear) a Cell’s Background Color
Written by
Reviewed by
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.
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.
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