VBA basics
33. Format Cells (Font)

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

33. Format Cells (Font)

Some of this topic was processed by machine translation. Original

This is the macro VBA specification method for the typeface of the character displayed in the cell.
The font of the cell (Range object) is the Font property.

The Font property returns a Font object.
I think it's hard to understand.
Objects are hierarchical,
When referencing lower level objects,
It is via a property that returns that object.
In other words, the Font object is handled via the Font property.

Let ’s leave the difficult reasoning,
Understand that Range.Font can handle cell fonts.

Specification in macro

Range.Font.property = setting value
The Font object has the following properties:

Property Property description Property setting value and description
Name Font name
FontStyle Font Style Use Bold and Italic below.
Bold Bold True、False
Italic Italic True、False
Size Font size
Strikethrough Horizontal strikethrough True、False
Superscript Superscript True、False
Subscript Subscript True、False
OutlineFont Outline font True、False
Shadow Shadow font True、False
Underline Underline type xlUnderlineStyleNone
Color Font color Long integer representing RGB value *1
ColorIndex Font color Index of 56 color palettes up to 2003
t is better to use Color because it is difficult to understand what color it is
ThemeColor theme color 2007 and later only
TintAndShade Brighten or darken 2007 and later only
ThemeFont theme font 2007 and later only

Some properties have been added since Excel 2007.
In 2007, the color has been greatly enhanced, so the properties have been added accordingly.
Themes should be set in the worksheet, not the macro.

Color constant ... *1

VBA provides constants that represent colors.

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

RGB functions are provided as functions for creating RGB values.
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

Cancel color specification (automatic)

To return the color specification to automatic,
Range.Font.ColorIndex = xlAutomatic
The visual result will be black characters,
Please understand that "automatic" and "black" are different.

Notes on font settings

Fonts are very important when people look at an Excel sheet.
Even with the same data, the visibility is greatly different due to different fonts.
However, the ease of viewing varies from person to person and changes with the times.
In this way, the part without the correct answer
If you can easily change it at any time, the maintainability will be better.

The easiest thing to change is still the sheet.
In other words, rather than setting everything with macros,
It is often better to be able to set the font as freely as possible on the sheet.

Articles with the same theme "VBA basics"

30. General practice question 3
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

  • >
  • >
  • 33. Format Cells (Font)

  • 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