While preparing reports or dashboards in an Excel spreadsheet, there are many times you may need to set the background colors of cells. Using conditional formatting in excel may prove to be difficult and complicated. There are many ways we can change the background color of a cell, Range, or column to make the reports look clean and professional. One of the methods to achieve this is using VBA. use the Interior property to return an interior object or use the Interior object's color index to set the background color.
In this article, let's look at the steps you take when you want VBA to copy cell interior color in Excel.
Using Color Property
1. Open your Excel workbook.
2. Press the Alt + F11 keys to open VBA Editor.
3. In color properties, mention the cell you want to color to change the background color. For example,
Sub Color ()
Range ("B3")
End Sub
4. For you to change the background color of the cell, use the property "Interior." You will add this is the same line as Range.
5. Under interior property, there are several other methods and properties. To set the cell color, use the color property. Add this after Interior.
6. From the Range of 8 colors, select the color type as per your wish. For example;
Sub Color ()
Range ("B3"). Interior. Color = vbRed
End Sub
7. Run the code using F5 or manually. The interior color in B3 will change.
8. To add an RGB function, you will need to enter a mixture of red, green, and blue in numbers. For example, to change the font color, enter the code given below.
Using Color Index Property
Color Index Property is different from Color Property in Excel. The color property uses eight constant colors given by VBA. It also uses the RGB function to create shade.
Color Index property is limited to VBA only. It can hold values from 1 to 56, and the numbers hold different colors. To change the color, here is what you have to do.
1. Open your Excel workbook.
2. Press the Alt + F11 keys to open VBA Editor.
3. Go to the Insert tab and click Module to open a new module. In the new Module, write the subcategory of VBA Font Color. The code should factor in the cell you want to copy an interior color in. use the code below;
Sub VBAFontColor4 ()
Range ("cell"). Select
End Sub
4. In the second line, select the Font Function with Selection command. Here is how the code will look
Sub VBAFontColor4 ()
Range ("cell"). Select
Selection. Font.
End Sub
5. To select and apply a color code, we need to choose the ColorIndex function instead of color. You will write the same code and add this as the code's third line before the last command.
Selection. Font. ColorIndex = 'number that represents the color you want
6. Run the code manually or press F5 to execute the procedure. The interior color will change as per the code when done.
In case you want to select a wide range of values, let's say from 1 to 56. Copy the code below and paste it into the code window.
Sub sbPrintColorIndexColors()
Dim iCntr
For iCntr = 1 To 56
Cells (iCntr, 1).Interior.ColorIndex = iCntr
Cells (iCntr, 1) = iCntr
Next iCntr
End Sub
After you have finished this, save the file as a macro-enabled workbook and press F5to execute the procedure.
Conclusion
The article above is a guide on how to use VBA color Index to copy cell interior color. You can decide whether to use the Color property or Color Index Property method. Both ways will enable you to color a cell interior.