VBA basics
34. Format Cells (fill, interior)

Excel macro basics and applications, introduction to Excel VBA
last updated:09-01-2019

34. Format Cells (fill, interior)

Some of this topic was processed by machine translation. Original


The most effective way to make a cell stand out would be to fill it with a color,
This is a macro VBA explanation when filling a cell.


The fill (pattern) of the cell (Range object) becomes the Interior property.
The Interior property returns an Interior object.
In other words, you will work with Interior objects via the Interior property.
Handle the Interior object in Range.Interior, and specify the fill of the cell.

Interior specification with macro VBA

Range.Interior.property = setting value
And Interior objects have the following properties:



property Property description Property setting value and description
Color Fill color Long integer representing RGB value *1
ColorIndex Fill color Index of 56 color palettes up to 2003
It is better to use Color because it is difficult to understand what color it is
Pattern Fill pattern XlPattern Enumeration
PatternColor Pattern color Long integer representing RGB value *1
PatternColorIndex Pattern color Index of 56 color palettes up to 2003
It is better to use Color because it is difficult to understand what color it is

In 2007 and later, you can also specify gradation,
Since it becomes quite complicated, the explanation is omitted here.
Use macro recording if necessary.
Basically, however, it is recommended to set the gradation on the worksheet.

Color constant ... *1

Excel makes it easy to use color constants.

vbBlack Black
vbRed Red
vbGreen Green
vbYellow Yellow
vbBlue Blue
vbMagenta Magenta
vbCyan Cyan
vbWhite White

The above constants can be used instead of long integers representing RGB values.
In addition to color constants, it can also be specified using the XlRgbColor enumeration.

List of set values of Color property

RGB function

As a function to create RGB values,
RGB functions are provided.
RGB (red, green, blue)
Specify red, green, and blue from 0 to 255 respectively.



Range.Font.Color = RGB(255, 0, 0) 'Red
Range.Font.Color = RGB(255, 255, 255) 'White
Range.Font.Color = RGB(0, 0, 0) 'Black

No fill

To cancel the fill,
Range ("A1"). Interior.Color = xlNone
The result looks the same as a white fill,
Please understand that “white fill” and “no fill” are different.

Use with conditional formatting

In conditional formatting, you can change the color according to the data.
In many cases, it is often used because it is easier than painting with macro VBA.
However, conditional formatting is broken by inserting and deleting matrices,
It may contribute to the sheet becoming heavier.
In such a case,
In some cases, it may be more efficient and easier to maintain without using conditional formatting.



Articles with the same theme "VBA basics"

31. Format Cells (display format, NumberFormatLocal)
32. Format Cells (Alignment)
33. Format Cells (Font)
34. Format Cells (fill, interior)
35. Format Cells (ruled line, border)
36. General practice question 4
37. Specifying a book sheet
38. Set formula in cells
39. Clear the cells
40. Copy, cut and paste of cells
41. Copy cells and paste values (PasteSpecial)



  • >
  • >
  • 34. Format Cells (fill, interior)

  • If you find this site useful, please share and bookmark.


    I'm going to pay close attention to the description,
    If you have any mistakes or suggestions,「Contact Us」I hope you will let me know.
    Use the posted VBA code at your own risk.
    We are not liable for any damages such as data corruption.




    If you find this site useful, please share and bookmark.
    To the bottom of the text