VBA Excel: Check a Cell’s Format

VBA & macros · Updated November 20, 2026

To check a cell’s format in VBA, read the relevant property of its Range object: use Range("A1").NumberFormat for the number-format code, Range("A1").Interior.Color for its fill color, and Range("A1").Font properties for text formatting. If conditional formatting may affect what you see, inspect Range("A1").DisplayFormat as well.

What “cell format” means in VBA

Excel does not store every aspect of a cell’s appearance in one simple format value. Number display, font, fill, borders, alignment, and other settings are separate properties. A check therefore needs to target the aspect you care about. A cell can have the same number format as another cell but a different fill, or look identical while holding a different underlying value.

For example, a cell might contain the numeric value 0.25 and use the number format 0%, so Excel displays 25%. Another cell containing 0.25 could use 0.00 and display 0.25. The values match, but the number formats and displayed results do not. Conversely, two cells can both display 25% while one holds 0.25 and the other holds the text “25%.” Checking appearance alone does not establish that values or data types match.

Use a specific property rather than trying to infer formatting from what appears on the worksheet. This also makes VBA checks easier to maintain: a test for a date format can be separate from a test for a red fill or bold text.

Inspect a cell’s format with a short macro

The following macro reports useful properties for the currently selected cell in the VBA editor’s Immediate window. It reads information and does not change the worksheet. Before running any macro, save a copy of the workbook; changes made by VBA generally cannot be reversed with Ctrl+Z, so a backup is prudent even when adapting a read-only example.

Sub ReportActiveCellFormat()
    Dim target As Range

    ' Inspect the selected cell without changing it.
    Set target = ActiveCell

    Debug.Print "Cell: " & target.Address(External:=True)
    Debug.Print "Value2: " & target.Value2
    Debug.Print "NumberFormat: " & target.NumberFormat
    Debug.Print "NumberFormatLocal: " & target.NumberFormatLocal
    Debug.Print "Displayed text: " & target.Text
    Debug.Print "Font name: " & target.Font.Name
    Debug.Print "Font size: " & target.Font.Size
    Debug.Print "Bold: " & target.Font.Bold
    Debug.Print "Fill color: " & target.Interior.Color
    Debug.Print "Effective number format: " & _
        target.DisplayFormat.NumberFormat
End Sub

In Excel for Microsoft 365 on Windows, select a cell, press Alt+F11 to open the Visual Basic Editor, insert a standard module, and paste the code. If the Developer tab is available, you can also choose Developer > Visual Basic. Run the macro from the editor, then press Ctrl+G to show the Immediate window if it is not already visible. On Mac, use Developer > Visual Basic; the function-key shortcut can vary with keyboard settings. Excel for the web does not run VBA macros, so open the workbook in the desktop application to use this code.

The Text property returns what Excel displays for the cell, not its format definition. A narrow column can make a number appear as hash marks, for instance. The Value2 property reports the underlying value without Excel’s special VBA Date or Currency subtypes, while NumberFormat reports the format code that controls number display.

Check number formats and compare cells

For a direct comparison, compare the two cells’ NumberFormat properties. With cells A2 and B2 as the cells being checked, this macro reports whether their format codes match:

Sub CompareNumberFormats()
    Dim firstCell As Range
    Dim secondCell As Range

    ' Set the cells to compare.
    Set firstCell = ActiveSheet.Range("A2")
    Set secondCell = ActiveSheet.Range("B2")

    If firstCell.NumberFormat = secondCell.NumberFormat Then
        MsgBox "The number formats match."
    Else
        MsgBox "The number formats are different."
    End If
End Sub

This checks only the number format. It does not compare font, fill, borders, alignment, or the values. It also tests the format codes as strings; it is not a general-purpose test of whether two cells look identical in every context. To check another property, compare that property explicitly. For example, compare firstCell.Interior.Color with secondCell.Interior.Color for fill color, or compare firstCell.Font.Bold with secondCell.Font.Bold for bold formatting.

A format code may include sections for positive numbers, negative numbers, zero, and text, separated by semicolons. Date and time formats are also number formats: Excel stores dates as numbers and applies a date-style display code. As a result, do not assume a cell is a date just because it looks like one. Check both the stored value and its number format when the distinction matters.

NumberFormat versus NumberFormatLocal

NumberFormat returns a format code in the VBA/Excel macro language, while NumberFormatLocal returns the code as shown in the user’s local Excel language and regional conventions. This matters when a workbook is used on computers with different locale settings. Decimal and thousands separators, date conventions, and localized format names can differ.

For reusable VBA logic, NumberFormat is usually the better property to inspect or set because it is intended for VBA’s format-code conventions. Use NumberFormatLocal when you specifically need to report or interpret the code as the current user sees it. A comparison of local codes can produce different results across regional settings even when the displayed intent is similar.

Neither property tells you the cell’s value. If a report needs to classify dates, percentages, or currency, make the classification rule explicit and consider the value, the format code, and the workbook’s regional context rather than relying on one appearance-based test.

Conditional formatting and the appearance you see

Conditional formatting can change a cell’s visible font, fill, border, or number format when a rule is true. The cell’s ordinary properties, such as Interior.Color, describe its directly applied formatting; they may not describe the final appearance imposed by a conditional-formatting rule. For the effective appearance, use DisplayFormat, as in target.DisplayFormat.Interior.Color or target.DisplayFormat.Font.Color.

This distinction explains a common puzzle: the cell looks red, but checking its ordinary interior color does not return the red color expected. The red may come from a conditional-formatting rule rather than direct fill formatting. Inspect the rule in Excel through Home > Conditional Formatting > Manage Rules, and check the effective property through DisplayFormat in VBA.

DisplayFormat is useful in a running macro, but it is not suitable for every VBA context. In particular, Microsoft documents limitations on using it from a worksheet user-defined function. If a macro needs to assess the visible result, read DisplayFormat in a regular procedure instead of assuming a custom worksheet function can return the effective format.

Check format without VBA

For a one-time inspection, the Excel interface may be quicker than writing code. Select a cell and press Ctrl+1 on Windows to open the Format Cells dialog. The Number, Alignment, Font, Border, Fill, and Protection tabs show different parts of the cell’s direct formatting. You can also inspect common number formats in the Home tab’s Number group.

To compare cells visually, select each cell in turn and check the same relevant settings. The Format Painter can copy formatting, but it is not a diagnostic tool: using it changes formatting and may overwrite settings you wanted to preserve. If the visible result is controlled by conditional formatting, inspect the rules as well as the Format Cells dialog.

On Mac, the Format Cells dialog is also available from the Format menu, and the keyboard shortcut may depend on the keyboard configuration. Excel for the web provides many formatting controls, but it cannot run the VBA procedures shown here. For a repeatable check across many cells, a desktop VBA macro is more practical than opening dialogs one cell at a time.

Common problems and reliable checks

  • Testing the wrong property: NumberFormat checks number display, not fill color or font. Identify the exact formatting feature the rule is meant to validate.
  • Confusing display with content: Text reflects the displayed result, while Value2 reflects the underlying value. A display can be affected by column width and formatting.
  • Missing conditional formatting: Use DisplayFormat for effective appearance in a macro when a conditional rule may be involved.
  • Comparing only one cell when a range is mixed: A property read from a multi-cell range can be ambiguous if cells have different settings. Loop through individual cells when checking a range.
  • Assuming a code has only one section: Format codes can have multiple semicolon-separated sections and conditions. If you need to validate a specific format, compare the expected code or test a carefully defined rule rather than relying on a loose text match.
  • Running code in the wrong Excel version: VBA runs in the desktop Excel application, not Excel for the web. Workbook macro security settings may also prevent a macro from running until the file is trusted.

For a range audit, use a loop and write the results to a separate report area or the Immediate window rather than changing the cells being inspected. Test the procedure on a copy first, especially if you extend it to repair formatting. A check should identify mismatches; a repair macro is a separate operation and should make clear which properties it will alter.

Frequently Asked Questions

How do I check whether a cell is formatted as a date in VBA?
Read the cell’s NumberFormat property and compare it with the date-format rules your workbook expects. Do not treat appearance alone as proof that the value is a date; Excel dates are numeric values displayed with date formats, and custom codes can vary.

How can I check a cell’s background color?
For its directly applied fill, inspect Range("A1").Interior.Color. If conditional formatting may supply the visible fill, inspect Range("A1").DisplayFormat.Interior.Color in a regular macro instead.

What is the difference between Text and NumberFormat?
Text returns the cell’s displayed text, which can be affected by the column width. NumberFormat returns the code that controls how numeric values are displayed. Neither property is a substitute for checking the underlying value.

Can VBA check every part of a cell’s formatting?
Yes, but there is no single property that summarizes every setting. Check the relevant properties individually, such as Font, Interior, Borders, HorizontalAlignment, and NumberFormat. Use DisplayFormat when the effective appearance may be changed by conditional formatting.

Can I run a VBA format check in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the procedures; you can still inspect formatting using the web app’s available interface controls.

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.