Excel VBA Formatting Cells and Ranges Using the Macro Recorder
By formatting cells and ranges you can give your worksheets a better look. Formatting applies to the following:
- The number format: Numeric, Date, Scientific …
- Text Alignment: Horizontal, Vertical, Orientation …
- Text Control: Wrap Text, Shrink to fit, Merge Cells …
- Text Direction
- Font Type: Calibri, Times new Roman …
- Font Style: Regular, Italic …
- Font Size: 8, 9, 10 …
- Font Color
- Font Effects: Strike through, Superscript …
- Borders: Style, Color, …
- Fill: Color, Pattern …
- Protection: Locked, Hidden
- …
As you can see there are a lot of different formatting options in excel. Listing all of them along with their syntax and examples would require an entire book. Fortunately excel has provided a tool for figuring out the syntax of the different formatting options, the macro recorder. Using the macro recorder you can figure out how to apply different formattings to ranges.
Jump To:
- Formatting Cells and the Macro Recorder
- Formatting Cell Borders
- Formatting Cell Protection
- Formatting Cell Font
- Formatting Cell Alignment
- Changing Cells Number Format
Contents
Formatting Cells and the Macro Recorder:
Below I will explain how to use macro recorder to figure out the required syntax for a specific type of formatting:
Step 1: In the developer tab click on Record Macro button:
Step 2: Click Ok:
Step 3: Apply the desired formatting to a range of cells. In this example I will change the color of the range to yellow and a apply a thick border to the exterior and interior sides of the range:
Step 4: Stop the macro recorder
Step 6: Open the visual basic editor. On the project window you will see a new folder which has been created called “Modules”. In this folder there will be a new file “Macro1”. By clicking on it you will see the generated Code:
For this example the code shown below has been generated:
Sub Macro1()
'
' Macro1 Macro
'
'
Range("I11:P16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
Remember that the macro recorder, records your every action. So if you have the macro recorder on and you start doing random things, the final generated code will become very long. So when you are trying to record a macro try to avoid any unnecessary actions. The first line of code you see is:
Range("I11:P16").Select
You will have to replace “I11:P16” with the range you are trying to modify . Also instead of using the select command you could directly use the range object:
Range("I11:P16").Borders(xlDiagonalDown).LineStyle = xlNone
Range("I11:P16").Borders(xlDiagonalUp).LineStyle = xlNone
With Range("I11:P16").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
I have brought some examples below.
Formatting Cell Borders:
The following example creates a border on the left edge of cell A1. The pattern is continuous and thick
With Range("A1").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
I have covered this topic in detail in the article below:
Formatting Cell Protection:
The following example locks and hides cells A1:
Range("A1").Locked = True
Range("A1").FormulaHidden = True
Formatting Cell Font:
The following code changes the font in cell A1 to arial, bold with size 9, applies strike through and single line underline formatting:
With Range("A1").Font
'font type
.Name = "Arial"
'font style
.FontStyle = "Bold"
'font size
.Size = 9
'uncheck strikethrough
.Strikethrough = True
'uncheck superscript
.Superscript = False
'uncheck subscript
.Subscript = False
.OutlineFont = False
.Shadow = False
'select single line underscript
.Underline = xlUnderlineStyleSingle
'set color to yellow
.Color = 65535
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
For more information about formatting font properties please see the article below:
Formatting Cell Alignment:
The following code sets the horizontal alignment of cell A1 to left, justifies its vertices alignment. Checks the wrap text and merge cell options and applies a -45 degree orientation:
With Range("A1")
'set the horizontal alignement to left
.HorizontalAlignment = xlLeft
'set the verticle alignment to justify
.VerticalAlignment = xlJustify
'check the wrap text option
.WrapText = True
'apply a -45 deg orientation
.Orientation = -45
'check the merge cell option
.MergeCells = True
End With
For more information about cell alignment please see the article below:
Changing Cells Number Format:
The following code changes cells A1’s number format to a date with the mm/dd/yy format:
Range("A1").NumberFormat = "mm/dd/yy;@"
See Also:
If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website www.software-solutions-online.com