How to Change the Background Color in Excel VBA

In this tutorial, you will learn about how to color the background of a cell or range.

There are two easiest methods to change the background color of a cell in Excel VBA.

1. Interior.Color
2. Interior.ColorIndex

First Method: - Interior.Color

In this method, we will learn how to apply the background color.

Suppose that you want to color the background of range (“A1: B6”) in our Excel sheet.

See the image below:-

Now, we will write the VBA code to color the background of the range (“A1: B6”).

Go to the developer tab à Visual Basic à on visual basic editor window --> Go to Insert and click on “Module”.

Once the module is opened,

Write the following code in the inserted module:-

In the above image, we have applied the “Green” color by VBA coding.

The same way we can apply 8 standard colors by VBA coding.

These 8 standard colors are as follows:-

1.     Black (vbBlack)
2.     White (vbWhite)
3.     Blue (vbBlue)
4.     Red (vbRed)
5.     Pink (vbYellow)
6.     Green (vbGreen)
7.     Cyan vb(Cyan)
8.     Magenta (vbMagenta)

See all these 8 standard color codes in VBA:-

Method 2:- Interior.ColorIndex

This is the second method to color the background of a cell or range. In the previous method, we applied the 8 standard colors in the given range.

But in this method, we can apply the color from 1 to 56.

Every single digit has a specific color from 1 to 56, but after this number, this method does not work.

Now, we write our code to apply the color in a cell or range, see below:-

“Range(“A1:B6”).interior.ColorIndex=1, and the same way we can write our code from 1 to 56 numbers.

For example, see below:-

These are the two methods to color the background in Excel VBA.

I hope you find it useful.

Thanks

3 Easiest Way to Copy and Paste Data in Excel VBA

How to Change Font Color in Excel VBA