Description:
It is an interesting feature in excel, we can change background color of Cell, Range in Excel VBA. Specially, while preparing reports or dashboards, we change the backgrounds to make it clean and get the professional look to our projects.
Change Background Color of Cell Range in Excel VBA – Solution(s):
We can use Interior.Color OR Interior.ColorIndex properties of a Rage/Cell to change the background colors.
Change Background Color of Cell Range in Excel VBA – Examples
The following examples will show you how to change the background or interior color in Excel using VBA.
Example 1
In this Example below I am changing the Range B3 Background Color using Cell Object
Sub sbRangeFillColorExample1() 'Using Cell Object Cells(3, 2).Interior.ColorIndex = 5 ' 5 indicates Blue Color End Sub
Example 2
In this Example below I am changing the Range B3 Background Color using Range Object
Sub sbRangeFillColorExample2() 'Using Range Object Range("B3").Interior.ColorIndex = 5 End Sub
Example 3
We can also use RGB color format, instead of ColorIndex. See the following example:
Sub sbRangeFillColorExample3() 'Using Cell Object Cells(3, 2).Interior.Color = RGB(0, 0, 250) 'Using Range Object Range("B3").Interior.Color = RGB(0, 0, 250) End Sub
Example 4
The following example will apply all the colorIndex form 1 to 55 in Activesheet.
Sub sbPrintColorIndexColors() Dim iCntr For iCntr = 1 To 56 Cells(iCntr, 1).Interior.ColorIndex = iCntr Cells(iCntr, 1) = iCntr Next iCntr End Sub
Instructions:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Insert a new module from Insert menu
- Copy the above code and Paste in the code window
- Save the file as macro enabled workbook
- Press F5 to execute the procedure
- You can see the interior colors are changing as per our code
Change Background Color of Cell Range in Excel VBA – Download: Example File
Here is the sample screen-shot of the example file.
Download the file and explore how to change the interior or background colors in Excel using VBA.
need help in excel
In company there are 10000 employee and all having duty in A shift, B shift, C shift. Each shift of 08 hrs
i have to find those employee who work continuous 10 days or more then 10 days
please help me to find the solution using VB macro in excel or any other way to find from huge no of data in excel
Kindly provide me the solution.
Harbinder Singh
its dependes of your database, can check with a vba code for filter based on the criteria, select the range and then color it,
Regards!
Thanks! This is excellent. It allows me to control the colors much more reliably than conditional formatting.