Excel VBA to Change Cell Background Color (2 Suitable Examples)

You may change the cell background color easily from the Home tab of the Excel worksheet by changing the Fill Color of a cell. But do you know that you can perform this task by applying VBA code too? Today I will show you how to change the cell background color with VBA in Excel. You will also learn to match the color of one cell with another and change it with the matching one.

📁 Download Practice Workbook

You can download the practice book from the link below.

2 Practical Cases to Change Cell Background Color with VBA in Excel

In this section, you will find 2 practical cases to change the background color of a cell in Excel. Let’s demonstrate them one by one.

1. VBA to Change Cell Background Fill with Any Color Specified Within Code

Let’s consider the following image.

The background color of these cells is in default white color. We want to change the background color of the range of these cells to Red, Green, and Blue.

Let’s see how you can perform this task.

1.1. Applying Range() Method

One method available here for changing cell background color is the Range() Method, where you can consider a range of cells at a single time and you don’t need to apply the command multiple times in VBA code. For applying the range method, you can use either Interior.Color or Interior.ColorIndex command.

1.1.1. Using Interior.Color Command

Let’s proceed with the following steps for utilizing this command.

Step 1: Create a Sub Procedure

• First, press ALT+F11 to open the Visual Basic Editor window.
• After opening the window, select Insert and click Module.

• Hence, the Module window will appear.

• Here, create a sub-procedure (i.e. Changing_Cell_Color)

Sub Changing_Cell_Color()

End Sub

Step 2: Assign Code

Then assign the code here. We have used the Interior.Color command for changing colors.

Code:

Sub Changing_Cell_Color()

Range("C5:E5").Interior.Color = vbRed
Range("C6:E6").Interior.Color = vbGreen
Range("C7:E7").Interior.Color = vbBlue

End Sub

Step 4: Run the Code

• Now, switch back to the worksheet and press ALT+F8 to run the code.
• From, the Macro command box, select the name of the macro and click Run.

• Hence, you will see that the background of these cells has been changed as per your command.

Read More: How to Change Background from Black to White in Excel (3 Ways)

1.1.2. Using Interior.ColorIndex Command

You can also use the Interior.ColorIndex command for changing the cell color. Every color is associated with a ColorIndex number. For example, the ColorIndex number of Red is => 3. The maximum value of ColorIndex number is 56. Here, the color Index will be used in the command. The code you should use here is:

Code:

Sub Changing_Cell_Color()

Range("C5:E5").Interior.ColorIndex = 3
Range("C6:E6").Interior.ColorIndex = 4
Range("C7:E7").Interior.ColorIndex = 5

End Sub

This procedure will provide you with the same result as Method 1.1.1.

Read More: How Do I Change the Default Background Color in Excel

Similar Readings

• How to Make Excel Look Pretty (16 Easy Formats)
• Excel VBA to Open Workbook in Background (2 Useful Examples)
• How to Make Excel Easier on the Eyes (12 Quick Tips)
• Make Picture Background Transparent in Excel (2 Methods)
• How to Remove Background in Excel (2 Practical Cases)

1.2. Using Cells Method

Another method you can use to change the background color of a cell is the Cells() Method. But there is a drawback to using this method. While assigning code, you can’t perform the task for a range of cells, rather you have to use cell references individually.

We will change the background color of single cells.

For this, you can use the code below.

Code:

Sub Changing_Cell_Color2()

Cells(5, 3).Interior.ColorIndex = 3
Cells(6, 3).Interior.ColorIndex = 4
Cells(7, 3).Interior.ColorIndex = 5

End Sub

• Run the code and get the background color changed.

Read More: How to Make Excel Spreadsheet Colorful (6 Simple Tricks)

2. Change Background Color of One Cell to Match with Another

Let’s consider another scenario. You may need to match a cell color with another one and change its color according to the matching color.

Here, in the image, the cell colors of column B are Red, Green, and Blue respectively. We want to change the background color of cells in column D according to the colors of column B.

⏩ Steps:

• Here, assign the code below for matching the color of cells D5, D6, D7 with cells B5, B6, B7.

Code:

Sub Changing_Cell_Color4()

Range("D5").Interior.ColorIndex = Range("B5").Interior.ColorIndex
Range("D6").Interior.ColorIndex = Range("B6").Interior.ColorIndex
Range("D7").Interior.ColorIndex = Range("B7").Interior.ColorIndex

End Sub

• As a result, the cell color of cells  D5, D6, D7 will be changed to the cell colors of cell B5, B6, B7

Read More: [Fixed!] Background Color Not Changing in Excel (6 Solutions)

Things to Remember

• Use cell reference each time while applying the Cells() Method and matching cell color with another cell.
• The ColorIndex number must not exceed 56. otherwise, it will result in an error.

Conclusion

In this article, I have tried to show you some methods to change the cell background color with VBA in Excel. I hope this article has shed some light on your way to this. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. For more queries, kindly visit our website ExcelDemy. Have a great day!

Related Articles

• How to Print Background in Excel (With Easy Steps)
• Make an Excel Spreadsheet Look Like a Form
• How to Change Background in Excel Sheet (4 Suitable Ways)
• Remove Background Color in Excel (5 Ways)
• How to Make Excel Look Like a Page (with Easy Steps)
• Change Worksheet Tab Color in Excel (5 Handy Methods)
• How to Make Excel Look Like an Application (with Easy Steps)