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:

'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

'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:

'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.

'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