VBA Cells Format – Formatting Excel Cells using VBA

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

In this tutorial
Let’s now focus on formatting Excel cells. Below find multiple examples of different formatting options:

Formatting Code
Interior
'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

Read more on the ColorIndex here: link

Font
'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
Borders
'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
Alignment
'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