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

Get FREE Advanced Excel Exercises with Solutions!

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.


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

Excel VBA Cell Background Color

 💡 Code Explanation

↪️ Here, Interior.Color => vbRed has been used for cell range C5:E5

So the background color of these cell ranges will be changed to Red.

↪️ Similarly, cell C6:E6 will change to Green and C7:E7 will change to Blue.

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.

Excel VBA Cell Background Color


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

Excel VBA Cell Background Color

 💡 Code Explanation

↪️ Here, the color index no of  Red, Green, and Blue are 3, 4, 5 respectively. So, 3 has been used as the Interior.ColorIndex for cell range C5:E5

So the background color of these cell ranges will be changed to Red.

↪️ Similarly, cell C6:E6 will change to Green and C7:E7 will change to Blue 

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

Read More: Excel VBA ColorIndex Chart


1.2. Using Cells Method

Another method you can use to change the background color of a cell is the Cells() Method. However, 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

Excel VBA Cell Background Color

 💡 Code Explanation

↪️ Cells([RowIndex],[ColumnIndex]) => the first property inside the Cells command is the Row Index number and the second property is the Column Index number.

↪️ So, Cells(5,3) denotes the cell at the intersection of row no 5 and column no 3. For this cell, the ColorIndex has been used => 3 which is the ColorIndex of Red. So, this cell will change to Red.

  • Run the code and get the background color changed.

Excel VBA Cell Background Color

Read More: Excel Color Index Numbers


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

Excel VBA Cell Background Color

Note: You can’t use cell range (i.e. D5:D7) in this code for matching color. You have to use cell references each time (i.e. D5, D6, D7).

 💡  Code Explanation

↪️ Here, the Interior.ColorIndex of cell D5 is matched with the color index of cell B5. So, the cell color D5 will be changed to the color of cell B5

  • 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: Excel VBA to Copy Cell Background Color


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.

📁 Download Practice Workbook

You can download the practice book from the link below.


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.

Have a great day!


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo