KISS Excel VBA
Keep It Super Simple with Excel VBA
Module_03_Format_Cells
Copy and paste the below into a module in VBA
Sub ClearFormats()
Cells.ClearFormats
End Sub
'--------------------------------------------------------------------------
Sub Font_Name()
'Makes the selection Calibri
Selection.Font.Name = "Calibri"
End Sub
'--------------------------------------------------------------------------
Sub Font_Bold()
'Makes the selection Bold ON/OFF
If Selection.Font.Bold = False Then
Selection.Font.Bold = True
Else
Selection.Font.Bold = False
End If
End Sub
'--------------------------------------------------------------------------
Sub Font_Italic()
'Makes the selection Italic ON/OFF
If Selection.Font.Italic = False Then
Selection.Font.Italic = True
Else
Selection.Font.Italic = False
End If
End Sub
'--------------------------------------------------------------------------
Sub Font_Underline()
'Makes the selection Underline ON/OFF
If Selection.Font.Underline = xlUnderlineStyleSingle Then
Selection.Font.Underline = xlUnderlineStyleNone
Else
Selection.Font.Underline = xlUnderlineStyleSingle
End If
'Selection.Font.Underline = xlUnderlineStyleSingle
'Selection.Font.Underline = xlUnderlineStyleDouble
'Selection.Font.Underline = xlUnderlineStyleNone
End Sub
'--------------------------------------------------------------------------
Sub Font_Size_increase()
'Increase Font Size
Selection.Font.Size = Selection.Font.Size + 1
End Sub
'--------------------------------------------------------------------------
Sub Font_Size_decrease()
'Decrease Font Size
Selection.Font.Size = Selection.Font.Size - 1
End Sub
'--------------------------------------------------------------------------
Sub Font_other_properties()
'Strikethrough, Superscript, Subscript, OutlineFont, Shadow, ThemeColor, TintAndShade, ThemeFont
With Selection.Font
'.FontStyle = "Regular" '"Bold Italic"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
End Sub
'--------------------------------------------------------------------------
Sub Number_General()
'Format as General
Selection.NumberFormat = "General"
End Sub
'--------------------------------------------------------------------------
Sub Number_Number_no_decimals_separator()
'Format as number, zero decimals with separator
Selection.NumberFormat = "#,##0"
End Sub
'--------------------------------------------------------------------------
Sub Number_Number_2_decimals_separator()
'Format as number, 2 decimals with separator
Selection.NumberFormat = "#,##0.00"
End Sub
'--------------------------------------------------------------------------
Sub Number_Number_3_decimals_no_separator()
'Format as number, 3 decimals without separator
Selection.NumberFormat = "0.000"
End Sub
'--------------------------------------------------------------------------
Sub Number_Currency_English_US()
'Format as Currency (English US), 2 decimals with separator
Selection.NumberFormat = "[$$-409]#,##0.00" '$ English US
End Sub
'--------------------------------------------------------------------------
Sub Number_Currency_€123()
'Format as Currency (€123), 2 decimals with separator
Selection.NumberFormat = "[$€-2] #,##0.00" ' EUR 123
End Sub
'--------------------------------------------------------------------------
Sub Number_Currency_123€()
'Format as Currency (123€), 2 decimals with separator
Selection.NumberFormat = "#,##0.00 [$€-1]" '123 EUR
End Sub
'--------------------------------------------------------------------------
Sub Number_Date_Short()
'Format date as dd/mm/yyyy
Selection.NumberFormat = "dd/mm/yyyy"
'Selection.NumberFormat = "dd/mm/yyyy;@"
End Sub
'--------------------------------------------------------------------------
Sub Number_Date_Full()
'Format date as full text date (month in text)
Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
End Sub
'--------------------------------------------------------------------------
Sub Number_Date()
'Format now as dd/mm/yyyy
MsgBox Format(Now, "dd/mm/yyyy")
End Sub
'--------------------------------------------------------------------------
Sub Number_Time_AMPM()
'Selection.NumberFormat = "[$-409]hh:mm:ss AM/PM;@"
Selection.NumberFormat = "[$-409]h:mm:ss AM/PM;@"
End Sub
'--------------------------------------------------------------------------
Sub Number_Time_seconds()
'Selection.NumberFormat = "hh:mm:ss;@"
Selection.NumberFormat = "h:mm:ss;@"
End Sub
'--------------------------------------------------------------------------
Sub Number_Time_minutes()
Selection.NumberFormat = "h:mm;@"
End Sub
'--------------------------------------------------------------------------
Sub Number_Percentage()
Selection.NumberFormat = "0.00%"
'Selection.NumberFormat = "0.000%"
''Fraction
'Selection.NumberFormat = "# ?/?" 'fraction: up to one digit
'Selection.NumberFormat = "# ??/??" 'fraction: up to two digits
'Selection.NumberFormat = "# ???/???" 'fraction: up to three digits
'Selection.NumberFormat = "# ?/2" 'as halves
'Selection.NumberFormat = "# ?/4" 'as quarters
'Selection.NumberFormat = "# ?/8"
'Selection.NumberFormat = "# ??/16"
'Selection.NumberFormat = "# ?/10"
'Selection.NumberFormat = "# ??/100"
''Scientific
'Selection.NumberFormat = "0.00E+00" '2 decimals
'Selection.NumberFormat = "0.000E+00" ' 3 decimals
End Sub
'--------------------------------------------------------------------------
Sub Number_Text()
'Format as text
Selection.NumberFormat = "@"
End Sub
'--------------------------------------------------------------------------
Sub Alignment_Horizontal_Left()
'Align Left
Selection.HorizontalAlignment = xlLeft
End Sub
'--------------------------------------------------------------------------
Sub Alignment_Horizontal_Center()
'Align Center
Selection.HorizontalAlignment = xlCenter
End Sub
'--------------------------------------------------------------------------
Sub Alignment_Horizontal_Right()
'Align Right
Selection.HorizontalAlignment = xlRight
End Sub
'--------------------------------------------------------------------------
Sub Alignment_Horizontal_General()
'Align General
Selection.HorizontalAlignment = xlGeneral
'Selection.HorizontalAlignment = xlFill
'Selection.HorizontalAlignment = xlJustify
'Selection.HorizontalAlignment = xlCenterAcrossSelection
'Selection.HorizontalAlignment = xlDistributed
End Sub
'--------------------------------------------------------------------------
Sub Alignment_Vertical_Top()
'Align Top
Selection.VerticalAlignment = xlTop
End Sub
'--------------------------------------------------------------------------
Sub Alignment_Vertical_Center()
'Align Center
Selection.VerticalAlignment = xlCenter
End Sub
'--------------------------------------------------------------------------
Sub Alignment_Vertical_Bottom()
'Align Bottom
Selection.VerticalAlignment = xlBottom
'Selection.VerticalAlignment = xlJustify
'Selection.VerticalAlignment = xlDistributed
End Sub
'--------------------------------------------------------------------------
Sub Alignment_WrapText()
' WrapText ON/OFF
If Selection.WrapText = False Then
Selection.WrapText = True
Else
Selection.WrapText = False
End If
End Sub
'--------------------------------------------------------------------------
Sub Alignment_Orientation()
Selection.Orientation = 0
'Selection.Orientation = xlVertical
End Sub
'--------------------------------------------------------------------------
Sub Alignment_IndentLevel_increase()
Selection.IndentLevel = Selection.IndentLevel + 1
End Sub
'--------------------------------------------------------------------------
Sub Alignment_IndentLevel_decrease()
Selection.IndentLevel = Selection.IndentLevel - 1
End Sub
'--------------------------------------------------------------------------
Sub Alignment_ShrinkToFit()
'Shrink To Fit ON/OFF
If Selection.ShrinkToFit = False Then
Selection.ShrinkToFit = True
Else
Selection.ShrinkToFit = False
End If
End Sub
'--------------------------------------------------------------------------
Sub Alignment_ReadingOrder()
'ReadingOrder: Context
Selection.ReadingOrder = xlContext
'Selection.ReadingOrder = xlLTR 'left to right
'Selection.ReadingOrder = xlRTL 'right to left
End Sub
'--------------------------------------------------------------------------
Sub Alignment_MergeCells()
'Merge Cells ON/OFF
If Selection.MergeCells = False Then
Selection.MergeCells = True
Else
Selection.MergeCells = False
End If
End Sub
'--------------------------------------------------------------------------
Sub Format_Font()
'Makes the selection Bold Calibri 11
Dim Format_range As Range
Set Format_range = Selection
With Format_range.Font
.Name = "Calibri"
.Size = 11
.Bold = True
End With
End Sub
'--------------------------------------------------------------------------
Sub Format_cell_Center()
'Makes the text in the cell to be in the center
Dim Format_range As Range
Set Format_range = Selection
With Format_range
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
'--------------------------------------------------------------------------
Sub Format_Borders()
'Draws all borders on selection
Dim Format_range As Range
Set Format_range = Selection
Format_range.Borders(xlDiagonalDown).LineStyle = xlNone
Format_range.Borders(xlDiagonalUp).LineStyle = xlNone
With Format_range.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic '1
.TintAndShade = 0 '-0.35
.Weight = xlThin
End With
With Format_range.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Format_range.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Format_range.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Format_range.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Format_range.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End Sub
'--------------------------------------------------------------------------
Sub Format_Interior_color()
'How to format the interior color of a cell
Dim Format_range As Range
Set Format_range = Selection
With Format_range.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = -0.5
.PatternTintAndShade = 0
End With
With Format_range.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
End Sub
'--------------------------------------------------------------------------
Sub Colors_Font()
'Codes of colors
'Standard colors
'range("E2").Select
With Selection.Font
.Color = -16777024
.TintAndShade = 0
End With
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
With Selection.Font
.Color = -16727809
.TintAndShade = 0
End With
With Selection.Font
.Color = -16711681
.TintAndShade = 0
End With
With Selection.Font
.Color = -11480942
.TintAndShade = 0
End With
With Selection.Font
.Color = -11489280
.TintAndShade = 0
End With
With Selection.Font
.Color = -1003520
.TintAndShade = 0
End With
With Selection.Font
.Color = -4165632
.TintAndShade = 0
End With
With Selection.Font
.Color = -10477568
.TintAndShade = 0
End With
With Selection.Font
.Color = -6279056
.TintAndShade = 0
End With
'themecolors
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = -4.99893185216834E-02
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349986266670736
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.499984740745262
End With
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0.499984740745262
End With
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0.349986266670736
End With
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0.249977111117893
End With
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0.149998474074526
End With
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 4.99893185216834E-02
End With
With Selection.Font
.ThemeColor = xlThemeColorDark2
.TintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorAccent3
.TintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
With Selection.Font
.Color = -10197916
.TintAndShade = 0
End With
End Sub
'--------------------------------------------------------------------------
Sub Format_table1()
ActiveWindow.DisplayGridlines = False
ActiveSheet.UsedRange.Select
' Range("A1").Select
' Range(Selection, Selection.End(xlToRight)).Select
' Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Font
.Name = "Calibri"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
' Range("A1").Select
' Range(Selection, Selection.End(xlToRight)).Select
Rows(1).Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10092543
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").ColumnWidth = 20
Columns("C:I").EntireColumn.AutoFit
'Columns("D:D").EntireColumn.AutoFit
Columns("E:E").NumberFormat = "m/d/yyyy"
Columns("F:I").NumberFormat = "#,##0.00"
Range("A1").AutoFilter
Range("B2").Select
ActiveWindow.FreezePanes = True
Range("A1").Select
End Sub