To format a worksheet with Excel VBA, reference the worksheet and set properties such as Font, Interior, NumberFormat, alignment, or column width. For example, a macro can bold and color a header range with a few lines of code; save a copy of the workbook before running any macro, because VBA changes cannot be undone with Ctrl+Z.
What worksheet formatting means in VBA
In Excel, a worksheet is a grid of cells, and formatting controls how those cells look and display values. VBA, Excel’s built-in programming language, lets you apply that formatting through an object model: a workbook contains worksheets, a worksheet contains ranges, and a range exposes formatting properties. You can target one cell, a selected block, a whole row or column, or the cells Excel considers used.
Formatting changes presentation rather than the underlying value. A date cell can display as “Nov 3” while still containing a date serial number; a currency format can show a numeric value with a currency symbol without changing the number itself. This distinction matters when you later sort, calculate, or export data. Likewise, a cell’s fill color is separate from its value and formula.
Most VBA worksheet-formatting work uses the Range object and its properties, including Font, Interior, Borders, HorizontalAlignment, WrapText, and NumberFormat. The key is to identify the correct worksheet and range explicitly before changing properties.
Set up and run a formatting macro
In Excel for Microsoft 365 on Windows, open the workbook, choose Developer > Visual Basic, then in the Visual Basic Editor choose Insert > Module. If Developer is not visible, enable it under File > Options > Customize Ribbon. Paste a procedure into the module and run it from the editor or Excel’s Developer > Macros command. On Mac, the Visual Basic Editor is available from the Developer tab when enabled; the keyboard shortcut differs, and some keyboards require Fn with Option+F11.
A macro should say which workbook and worksheet it means. ThisWorkbook refers to the workbook containing the macro, while ActiveWorkbook refers to the workbook currently active in Excel. A bare reference such as Range("A1") depends on the active sheet, which may not be the one intended. Qualifying the range through a worksheet variable prevents that ambiguity.
Before running any example macro, save a separate copy of the workbook. A macro’s formatting changes are not reversed by Ctrl+Z, and a mistaken reference can affect the wrong sheet. On Windows, Alt+F11 opens the VBA editor; on Mac, Option+F11 is commonly used, subject to keyboard settings.
Format a header range
This example formats the first row of a report across columns A through F. It assumes the worksheet named Report exists in the same workbook that contains the macro. The fill uses a built-in RGB color, and the font color is set separately so the text remains readable against the fill.
Sub FormatReportHeader()
Dim ws As Worksheet
Dim header As Range
' Target the sheet in the workbook containing this macro.
Set ws = ThisWorkbook.Worksheets("Report")
Set header = ws.Range("A1:F1")
' Apply a consistent header style.
With header
.Font.Bold = True
.Font.Color = RGB(255, 255, 255)
.Interior.Color = RGB(31, 78, 121)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
ws.Rows(1).RowHeight = 30
End Sub
The With block applies several settings to the same range without repeating its full reference. RGB(red, green, blue) uses component values from 0 to 255. Here, white text sits on a dark blue fill. Setting WrapText allows longer headings to use more than one line, and the row height gives the wrapped text room. If the report has a different number of columns, adjust the range rather than formatting extra cells unnecessarily.
Apply number formats, borders, and alignment
Use NumberFormat when the goal is to change how numeric values appear. The format string follows Excel’s number-format conventions, not a VBA-specific date or currency conversion. For example, "#,##0.00" displays two decimal places, while "mmm d, yyyy" displays a date with an abbreviated month. A format does not turn text that resembles a number into a numeric value; data type and display format are separate issues.
This second example assumes dates are in B2:B100 and amounts in C2:C100 on the Report sheet. Change the ranges to match the actual layout. The code also adds a thin border around the amount cells, which can help distinguish a table without applying borders to an entire worksheet.
Sub FormatReportData()
Dim ws As Worksheet
Dim dates As Range
Dim amounts As Range
' Target the sheet in the workbook containing this macro.
Set ws = ThisWorkbook.Worksheets("Report")
Set dates = ws.Range("B2:B100")
Set amounts = ws.Range("C2:C100")
' Display dates and numeric amounts consistently.
dates.NumberFormat = "mmm d, yyyy"
amounts.NumberFormat = "$#,##0.00"
amounts.HorizontalAlignment = xlRight
' Add a bottom border to the amount cells.
With amounts.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.Color = RGB(200, 200, 200)
End With
End Sub
The dollar sign in this example is literal; it does not automatically adapt to a user’s preferred currency or locale. For a workbook used across regions, consider whether a fixed symbol is appropriate, or choose a suitable built-in number format in Excel and inspect its format code before using it in VBA. Excel can also display negative numbers, zeros, and percentages differently through custom format sections.
Set column widths and row heights
Column width and row height are worksheet-level layout settings rather than ordinary font or fill attributes. AutoFit adjusts a row or column to fit its contents. It is useful after writing or formatting data, but it can produce very wide columns if one cell contains a long note. For a consistent report, a fixed width or a maximum-width strategy may be more appropriate.
Sub SizeReportColumns()
Dim ws As Worksheet
' Target the sheet in the workbook containing this macro.
Set ws = ThisWorkbook.Worksheets("Report")
' Fit the report columns to their contents.
ws.Columns("A:F").AutoFit
' Keep the description column at a readable fixed width.
ws.Columns("D").ColumnWidth = 28
' Give the heading room for wrapped labels.
ws.Rows(1).RowHeight = 30
End Sub
Run AutoFit after applying the desired font and wrapping settings, since those affect how much space the content needs. If the sheet contains merged cells, AutoFit may not size them as expected. In that case, set a practical width or height directly, or redesign the layout to avoid merged cells in data tables.
Choose the right range and avoid unnecessary formatting
The scope of a formatting macro is one of the most important decisions. ws.Range("A1:F1") is predictable when the layout is fixed. ws.Columns("A:F") deliberately formats entire columns, which may be suitable for column-wide number formats but can be wasteful for settings applied to every cell. A full worksheet contains a very large grid, so applying detailed formatting indiscriminately can increase file size and slow workbook operations.
UsedRange returns the area Excel regards as used on a worksheet, but it can include cells that were formatted previously even if they now appear blank. It is not always the same as the visible data block. For a regularly changing table, a named range or an Excel table’s data range can provide a clearer target. If you use a fixed address, document what rows and columns it expects so the next person can maintain the macro safely.
- Use a specific address for stable layouts, such as a known report header.
- Use whole columns when the formatting genuinely applies to every row in those columns.
- Use a table or a carefully determined last row when the amount of data changes over time.
- Avoid selecting cells with
Selectjust to format them; direct references are clearer and do not depend on the user’s selection.
Formatting can also make a workbook harder to use if it is excessive. Apply color to communicate meaning, not as decoration alone, and maintain sufficient contrast. Do not rely on color as the only way to identify a status; add text or another visual cue for readers who may not distinguish the colors.
Reusable styles, conditional formatting, and VBA choices
For a repeated, static appearance, Excel cell styles can be easier to maintain than a long collection of property assignments. VBA can apply a style by name, provided that style exists in the workbook. Direct properties are more explicit and portable when you need exact colors, borders, or number formats; styles are convenient when a consistent design should be changed centrally.
Conditional formatting is different from ordinary formatting. A normal VBA assignment sets a cell’s appearance directly, while a conditional-formatting rule changes the appearance when a condition is met. For example, a static red fill remains red until another macro changes it; a conditional rule can highlight a value only while it meets a criterion. Excel’s ribbon path for configuring rules is Home > Conditional Formatting. VBA can create or modify rules too, but that requires working with a range’s FormatConditions collection and defining the rule correctly.
For a one-time cleanup, using the ribbon may be simpler and easier to review. VBA is especially useful when the same formatting must be repeated across many workbooks, applied after a data refresh, or combined with other automation. If a workbook is shared with people who cannot enable macros, consider applying formatting manually, using an Excel table style, or providing a macro-free copy of the finished file.
Common problems and safe troubleshooting
A frequent source of errors is a worksheet name that does not match exactly. The tab name in Worksheets("Report") must be spelled as it appears in the workbook, including spaces. Another common problem is targeting the wrong workbook with ActiveWorkbook or relying on the active sheet. Use ThisWorkbook when the intended sheet is stored in the macro workbook, and use a deliberately assigned workbook variable when the macro should edit a different file.
Other issues often come from applying the right property to the wrong range. For example, setting NumberFormat does not change text into a date, and changing Font.Color does not change the cell fill. If a change does not appear, check whether conditional formatting is overriding the visible appearance, whether the target range is correct, and whether the worksheet is protected. A protected sheet may prevent formatting changes unless the macro has the required permissions or protection handling.
Macros can be blocked by security settings or by saving the file in a format that does not preserve VBA. Save a workbook containing macros as an Excel Macro-Enabled Workbook (.xlsm), and enable macros only for files you trust. Excel for the web can open macro-enabled workbooks, but it cannot create, run, or edit VBA macros; use the desktop Excel app for this task. VBA is available in Excel for Windows and Mac, but platform-specific references or controls may not behave identically, so keep formatting code to the standard Excel object model when portability matters.
Frequently Asked Questions
How do I format an entire worksheet in Excel VBA?
Reference the worksheet, then apply a property to ws.Cells, such as ws.Cells.Font.Name or ws.Cells.NumberFormat. Use whole-sheet formatting sparingly: it can add unnecessary formatting to a large number of cells. For most reports, format the specific used data range instead.
How do I format a cell as a date using VBA?
Set the target range’s NumberFormat property to a date format such as "mmm d, yyyy". The cell must contain a date value for it to display as a date; setting the format alone does not convert arbitrary text into a valid date.
Can VBA format a worksheet without selecting cells?
Yes. Qualify the range through a worksheet object, as in ws.Range("A1:F1").Font.Bold = True. Direct references are generally more reliable than selecting a sheet or range, because they do not depend on what is active in Excel.
Why does my worksheet formatting macro say Subscript out of range?
This commonly means VBA cannot find the worksheet name used in the code, or the code is looking in a different workbook than expected. Check the tab name and whether ThisWorkbook or ActiveWorkbook is appropriate for the file you intend to format.
Can I undo a VBA formatting change with Ctrl+Z?
No. Running a VBA macro clears Excel’s regular undo history, so Ctrl+Z does not reverse the macro’s changes. Save a copy before running unfamiliar code, and test the macro on a duplicate workbook or a small range first.
Does VBA formatting work in Excel for the web?
No, Excel for the web does not run or edit VBA macros. Use the Excel desktop application for Windows or Mac to run VBA. A workbook can still be opened in the browser, but its macro automation must be run in the desktop app.
Related
- Flash Fill Excel Practice: Examples and Tips
- Flash Fill in Excel: How It Works and When to Use It
- Flash Fill in Excel: Fill Blanks or Detect Patterns
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Copy Paste Transpose: Working Code and TipsTo copy a range and paste it transposed with Excel VBA, copy the source range and use PasteSpecial…
- VBA Sort Data in Excel: How to Build a Reliable MacroYes. In Excel for Microsoft 365 on Windows, VBA can sort a range or table by one or…
- Macro Recorder Best Options for Excel and Windows PCsThe best macro recorder depends on what you want to automate: for repeated tasks inside Excel, start with…
- Excel VBA Current Date: Date, Now, Formatting, and ExamplesIn Excel VBA, use Date to get the computer’s current date, without a time; use Now to get…
- Excel VBA: Get the Current Workbook File Path EasilyIn Excel VBA, use ThisWorkbook.Path to get the folder path of the workbook that contains the code, or…
- Excel VBA Data Entry: Forms, Validation, and MacrosExcel VBA data entry uses Visual Basic for Applications to collect, check, and write information in a workbook.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.