Change the Background Colors in Excel VBA is an easy task.
In this blog post, you will learn about changing the background colors in Excel VBA.
In Excel, we change the background color by selecting the range, then go to the ‘Home’ tab then go to the ‘Font’ group and select the ‘Fill Color’ option.
Click the drop-down link and there are multiple colors, which you can apply into your selected range where you want to change the background color.
Here, you will learn about how to change the background colors with the help of VBA.
In VBA, you will learn to change the background color with three (3) easiest methods.
Change the Background Colors in Excel VBA – Using 8 Standard Colors
In this method, you can use 8 standard colors in the VBA. These color are as follows:-
| Black – vbBlack White – vbWhite Red -vbRed Blue – vbBlue Green – vbGreen Yellow – vbYellow Cyan – vbCyan Magenta – vbMagenta |
These are 8 standard colors, which we can apply with their names in VBA.
Change the Background Colors in Excel VBA, we need to use the above standard colors in VBA Code.
Now, we will apply these codes in one of our VBA macros to change the colors of background colors in Excel VBA within cells from range “A1” to “A10”.
| Sub Standard_Color() Range(“A1:A10”).Interior.Color = vbBlack Range(“A1:A10”).Interior.Color = vbWhite Range(“A1:A10”).Interior.Color = vbRed Range(“A1:A10”).Interior.Color = vbBlue Range(“A1:A10”).Interior.Color = vbGreen Range(“A1:A10”).Interior.Color = vbYellow Range(“A1:A10”).Interior.Color = vbCyan Range(“A1:A10”).Interior.Color = vbMagenta End Sub |
By executing the above VBA code by pressing “F8” step by step, our result will be shown as the below image:-
If you execute the above code by pressing “F8”, you will notice each execution will change the background colors.
Below is the another method to “Change the Background Colors in Excel VBA”.
Change the Background Colors – Using ColorIndex Method
In this method, there are 56 colors that we can apply to ‘change our background colors in Excel VBA,.
Each number from 1 to 56 has a particular color code.
When we will apply this method to change the background color, we will change the code ‘ColorIndex’ in place of ‘Color’.
Now we will apply for these numbers in the VBA editor window to change the background colors.
See the code in the VBA editor window:-
| Sub ColorIndex_Method() Range(“A1:A10”).Interior.ColorIndex = 1 Range(“A1:A10”).Interior.ColorIndex = 2 Range(“A1:A10”).Interior.ColorIndex = 5 Range(“A1:A10”).Interior.ColorIndex = 15 Range(“A1:A10”).Interior.ColorIndex = 25 Range(“A1:A10”).Interior.ColorIndex = 45 Range(“A1:A10”).Interior.ColorIndex = 55 Range(“A1:A10”).Interior.ColorIndex = 56 End Sub |
Now we will execute the above code by pressing “F8” again and again to execute each of the codes.
See below image for result:-
Here we have only applied some code to change the background colors in Excel VBA.
But, in this method, we can use the number from 1 to 56 to apply different colors.
Changing background color by RGB (Red, Green, Blue) Method
RGB is a short form of Red, Green, and Blue.
These are the basic or primary colors and by combining these colors we can create multiple other new colors.
This is our 3rd method to ‘change the background colors in Excel VBA.
When applying these colors in VBA can be written as RGB (255,255,255).
Values in the bracket are the color code of each color, where the range of each color from 0 to 255. Where “0” is the minimum and “255” is the maximum range.
We can create multiples of colors with the help of these three (3) color codes.
Between ranges we can put any number for any color combination.
See the VBA code in the VBA Editor window:-
| Sub Background_RGB_Method() Range(“A1:A10”).Interior.Color = RGB(0, 0, 0) ‘for Black Color Range(“A1:A10”).Interior.Color = RGB(0, 0, 255) ‘for Blue Color Range(“A1:A10”).Interior.Color = RGB(0, 255, 0) ‘ For green Color Range(“A1:A10”).Interior.Color = RGB(0, 255, 255) ‘For Cyan Color Range(“A1:A10”).Interior.Color = RGB(255, 0, 0) ‘For Red Color Range(“A1:A10”).Interior.Color = RGB(255, 0, 255) ‘For Magenta Color Range(“A1:A10”).Interior.Color = RGB(255, 255, 0) ‘For Yellow Color End Sub |
Now pressing “F8” to execute the above code step by step, we will get the following result.
See the image below:-
Here, you can see that how we change the background colors in Excel VBA by the RGB method.
Here, in this post, we have explained the 3 easiest methods to change the background colors in Excel VBA.
Furthermore, details for the Excel Interior Color (Background Color) are provided on the Microsoft Office website.
I hope you find this post useful.
Please feel free to put your comments or suggestion.
Thanks
Related Post