VBA Cells Format – Formatting Excel Cells using VBA

Formatting Excel Cells can be automated to save a lot of time. Below you can find code samples for formatting Excel cells.

Formatting Cell Interior

You have at least 2 options to set a Cell background color in VBA. The first is setting by using the Color property by setting it to a value using the VBA RGB function and specifying its R (Red), G (Green), B (Blue) components. The second is using the ThemeColor property and setting it to constant representing a default Theme color:

1
2
3
4
5
6
7
8
9
'Alternative ways to set the cell background color
With Range("A1").Interior
'Sets the background color to an RGB value (Red,Green,Blue)
.Color = RGB(255,0,0) 'Green
'Sets the background color to the Theme color accent 1 color
.ThemeColor = xlThemeColorAccent1
'Sets the background color to the ColorIndex
.ColorIndex = 1 '1=Black, 2=White, 3=Red ..etc. Check link below for more
End With

You can read more on the difference between the VBA ColorIndex and VBA Color property here.

Formatting Excel Fonts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
'Alternative ways to set the cell background color
With Range("A1").Font
'Sets the font color to RGB value. See Interior row above for more color options
.Color = RGB(0,255,0)
'Sets font as Italic
.Italic = true
'Sets font as bold
.Bold = true
'Sets the name of the font e.g. Arial, Times New Roman
.Name = "Agency FB"
'Set the size of the font
.Size = 11
'Sets strikethrough
.Strikethrough = True
'Sets as either super or subscript (mutually exclusive)
.Superscript = False
.Subscript = True
End With

Formatting Cell Borders

To set borders of Excel Cells in VBA you need to use the Borders property:

1
2
3
4
5
6
7
8
9
10
11
12
'Set all borders to continuous and thin
With Range("A1").Borders
.LineStyle = xlContinuous
.Weight = xlThin
'Sets the border color to RGB value. See Interior row above for more color options
.Color = RGB(0,255,0)
End With
'Set only top border as continuos and thin
With Range("A1").Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With

Possible LineStyles:

  • xlContinuous
  • xlDash
  • xlDashDot
  • xlDashDotDot
  • xlDot
  • xlDouble
  • xlSlantDashDot
  • xlLineStyleNone

Possible borders (Index):

  • xlDiagonalDown
  • xlDiagonalUp
  • xlEdgeBottom
  • xlEdgeLeft
  • xlEdgeRight
  • xlEdgeTop
  • xlInsideHorizontal
  • xlInsideVertical

Formatting Cell Alignment

Excel Cell alignment can be modified by a couple of ways. The first is using HorizontalAlignment and VerticalAlignment properties. Additionally you can set indentation using the InsertIndent procedure.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
'Increase or decrease cell indent
Range("A1").InsertIndent 1
Range("A1").InsertIndent -1
 
'Align to left, bottom
With Range("A1").
.HorizontalAlignment = xlLeft 'or xlRight
.VerticalAlignment = xlBottom 'or xlTop
End With
 
'Set wrap text to true
Range("A1").WrapText = True
 
'Set cell orientation to 45 degrees
Range("A1").Orientation = 45