Excel VBA: Change Cell and Range Formats

VBA & macros · Updated November 19, 2026

To change a cell’s format with Excel VBA, assign a property such as NumberFormat, Font.Bold, or Interior.Color to a Range. For example, Worksheets("Sheet1").Range("A1:C10").NumberFormat = "0.00" displays those cells with two decimal places without changing their stored values.

What VBA formatting changes

Excel stores a cell’s value separately from how it looks. VBA formatting changes the display and presentation: a number can appear as currency, a date can show a month name, or text can appear bold. Formatting does not normally convert the underlying value. For example, applying a percentage format to a cell containing 0.25 displays 25%, while the stored value remains 0.25.

In VBA, formatting is controlled through the Range object and its properties. A range can be one cell, a rectangular block, a whole row or column, or a noncontiguous selection. The same property assignment can apply to every cell in the selected range, which is useful for repeatable report layouts.

Common formatting properties include:

  • NumberFormat for numbers, dates, percentages, and other display formats.
  • Font for typeface, size, color, bold, and italic settings.
  • Interior for a cell’s fill color and pattern.
  • HorizontalAlignment and VerticalAlignment for positioning content.
  • Borders for lines around cells or along particular edges.

A basic macro to format a range

This example applies a light fill and consistent text alignment to a report area, then gives the header row a bold font and bottom border. Change the worksheet name and range to match your workbook.

Sub FormatReportRange()
    Dim ws As Worksheet
    Dim target As Range

    ' Set the worksheet and the report area to format.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set target = ws.Range("A1:D20")

    With target
        .Font.Name = "Aptos"
        .Font.Size = 10
        .Interior.Color = RGB(242, 242, 242)
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With

    ' Give the header a distinct appearance.
    With ws.Range("A1:D1")
        .Font.Bold = True
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
    End With
End Sub

The With block keeps the code readable: each property beginning with a dot applies to the object named after With. The macro does not need to select the worksheet or range first. Referring to the target directly is generally clearer and avoids changing the user’s active selection.

How to run the macro safely

In Excel for Microsoft 365 on Windows, save a backup copy of the workbook before running a macro. VBA formatting changes cannot be undone with Ctrl+Z, so a backup is the simplest way to recover if a macro affects the wrong cells. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm).

  1. Open the workbook and press Alt+F11 to open the Visual Basic Editor.
  2. Choose Insert > Module, then paste the macro into the new module.
  3. Return to Excel, press Alt+F8, select the macro, and choose Run. You can also run it from the editor with F5 while the cursor is inside the procedure.

On a Mac, the Visual Basic Editor can be opened from the Developer tab or Tools > Macro > Visual Basic Editor; the keyboard shortcut may depend on the keyboard and function-key settings. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Only run macros from sources you trust, and avoid enabling macros globally just to run an unfamiliar file.

Set number formats, dates, and percentages

Use NumberFormat when you want a repeatable display format. The format string is the same kind of code used in Excel’s Format Cells dialog. For example, 0.00 displays two decimal places, #,##0 displays a thousands separator with no decimal places, and mmm d, yyyy displays a date such as Jan 5, 2026. The right format depends on what the values mean; a date serial and an ordinary number can contain similar underlying values but need different displays.

This complete macro formats a column of amounts as currency-like values with two decimal places. The dollar sign is a literal character in this example; use the appropriate symbol and number format for your workbook.

Sub FormatAmounts()
    Dim ws As Worksheet
    Dim amounts As Range

    ' Format the amount cells on the Sales sheet.
    Set ws = ThisWorkbook.Worksheets("Sales")
    Set amounts = ws.Range("B2:B25")
    amounts.NumberFormat = "$#,##0.00"
End Sub

For percentages, the stored values matter. If B2 contains 0.125, setting its format to 0.0% displays 12.5%. If B2 contains 12.5, the same format displays 1250.0%; changing the format does not divide the value by 100. A custom format can also show negative numbers differently, such as red text, but that changes only their appearance.

NumberFormat uses Excel’s standard format-code conventions and is usually the better choice for portable VBA. NumberFormatLocal reads or sets a format code as it appears in the user’s regional language and settings. Because separators, names, and symbols can vary by locale, use the local version only when you specifically need to work with a localized format string.

Change fonts, fills, alignment, and borders

Font and fill settings are separate from number formats, so a macro can adjust one without disturbing the others. Use .Font.Color = RGB(0, 0, 128) for a dark-blue font, or .Interior.Color = RGB(255, 255, 0) for a yellow fill. The RGB function takes red, green, and blue values from 0 to 255. To remove bold emphasis, set .Font.Bold = False; to make text bold, set it to True.

Alignment properties include xlLeft, xlCenter, and xlRight for horizontal alignment, and xlTop, xlCenter, and xlBottom for vertical alignment. These affect the position of content within a cell, not the cell’s value. For borders, specify the edge you want, such as xlEdgeBottom, and assign a line style such as xlContinuous. Applying borders to every edge of every cell in a large range can create a dense grid, so choose edges deliberately.

When setting several properties, group them in a With block for the target range. Remember that applying a fill to a large range can cover existing colors, and assigning a font property across the whole range can overwrite intentional differences. Narrow the target range when only a heading, subtotal, or specific field needs the change.

Format a range that grows with your data

A fixed address such as B2:B25 is straightforward, but it will miss new rows added below row 25. If column A is continuously populated for each record, use its last nonempty row to determine how far the amount range should extend. The following macro handles an empty data area by exiting before it creates an invalid range.

Sub FormatAmountsToLastRow()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim amounts As Range

    ' Use column A to find the last row containing a record.
    Set ws = ThisWorkbook.Worksheets("Sales")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Stop if there are no data rows below the header.
    If lastRow < 2 Then Exit Sub

    Set amounts = ws.Range("B2:B" & lastRow)
    amounts.NumberFormat = "$#,##0.00"
End Sub

This approach assumes row 1 is a header, column A has no gaps that would hide later records, and the matching amounts are in column B. If column A can contain blanks or the data is an Excel table, choose a more reliable boundary for the actual layout. In particular, an entirely blank key column can make the last-row calculation point to the wrong place.

Copy or remove formatting

To copy formatting from one range to another with VBA, use PasteSpecial with xlPasteFormats. This copies formatting without replacing the destination’s values. The source and destination ranges do not have to contain the same values, but selecting a destination with a suitable shape makes the result easier to predict.

Sub CopyCellFormats()
    Dim ws As Worksheet
    Dim source As Range
    Dim destination As Range

    ' Copy the appearance of the first row to the next row.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set source = ws.Range("A1:D1")
    Set destination = ws.Range("A2:D2")

    source.Copy
    destination.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
End Sub

To remove direct cell formatting, select the relevant cells and choose Home > Clear > Clear Formats. This removes direct formats such as font, fill, borders, and number format, but it does not delete the cell’s contents. Be aware that clearing formats may remove useful formatting as well as the unwanted change. If you want to preserve the rest of a cell’s appearance, change only the specific property that needs correction instead.

Common problems and how to avoid them

  • The macro changes the wrong cells. Check the worksheet name and range address. A reference such as Range("A1") without a worksheet qualifier refers to the active sheet, which may not be the sheet you expect. Qualify it with a worksheet variable.
  • The displayed value looks wrong. Confirm both the stored value and the format code. A percentage format does not convert 12.5 into 12.5%; it displays 12.5 as 1250.0%. A narrow column may also show number signs for a value that is valid but does not fit.
  • A visible color or font does not match the macro. Conditional formatting can change a cell’s appearance when its rules apply. The direct formatting set by VBA may still be present underneath. Review Home > Conditional Formatting > Manage Rules before assuming the macro failed.
  • The procedure stops with an error. Check that the worksheet exists under the exact name used in the code, that the workbook structure permits the change, and that the target is not protected against formatting. A misspelled sheet name commonly causes a runtime error.
  • New rows keep the old appearance. A hard-coded range does not expand automatically just because data is added. Use a calculated last row, an Excel table, or a suitable event-based procedure if the format must adapt to future entries.

It is also useful to distinguish direct formatting from conditional formatting. Direct formatting is what properties such as Font.Color and Interior.Color set. Conditional formatting applies its own appearance according to rules, so the visible result can depend on the cell’s current value and the rule’s priority. Formatting a cell with VBA does not necessarily remove or replace those rules.

Frequently Asked Questions

How do I format an entire column with VBA?
Use a column range such as Worksheets("Sheet1").Columns("B").NumberFormat = "0.00". Applying formatting to a full column is convenient, but it also affects cells beyond the current data, so use a bounded range if that is not wanted.

Can VBA change a format without changing the cell value?
Yes. Assigning properties such as NumberFormat, Font.Bold, or Interior.Color changes how the cell is displayed, not its underlying value. A separate value assignment, such as setting .Value, is what changes the cell’s contents.

How can I format only cells that meet a condition?
A macro can loop through cells, test each cell’s value, and apply formatting to those that match. For appearance that should update automatically when values change, Excel’s conditional-formatting rules are often a better fit than a one-time macro.

Why can’t I undo a VBA formatting macro?
Running VBA clears Excel’s normal undo history, so Ctrl+Z cannot reverse the macro’s changes. Save a separate copy before running formatting code, and test it on a small range or sample workbook first.

Can I run these macros in Excel for the web?
No. Excel for the web can store and open macro-enabled workbooks, but it does not run VBA. Open the workbook in the desktop Excel application to run the macro; Excel for Microsoft 365 on Windows is the main context for the steps above.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.