Excel VBA Get Cell Format: Read & Use It

VBA & macros · Updated November 22, 2026

To get a cell’s format in Excel VBA, use the NumberFormat property of the Range object, for example: MsgBox Range("A1").NumberFormat. This returns a string like “General”, “0.00”, or “mm/dd/yyyy”, which you can read, compare, or assign to another cell. For visual formatting (font color, fill, borders), access properties like Font.Color, Interior.Color, and Borders.LineStyle.

Background: Why Read a Cell’s Format in VBA?

Excel stores two kinds of data in a cell: the value and the presentation of that value. The format controls how the value appears—dates as “14-Mar-2024” instead of a serial number, numbers with thousands separators, or text that is bold and red. When you automate reports, you often need to check whether a cell is already formatted correctly before applying changes, or you might copy formatting from one cell to another without copying values. VBA gives you full read access to these format settings, which is essential for building macros that respect existing templates or audit spreadsheets for consistency.

How to Read Number Formats

The most common format query is NumberFormat. It returns the format code as a string. For example:

Dim fmt As String
fmt = Range("B2").NumberFormat
MsgBox fmt   ' Shows e.g. "#,##0.00"

You can also use NumberFormatLocal to get the format in the user’s language (e.g., “jj/mm/aaaa” in French). To check if a cell uses a date format, you can test if the format string contains “y”, “m”, or “d” (but be careful—”m” can mean month or minute). A more reliable way is to use Range("A1").NumberFormat and compare against known date patterns, or use IsDate on the cell’s value as a supplementary check.

Reading Font, Fill, and Border Settings

Visual formatting is accessed through child objects of the Range. Here are the key properties:

  • Font: Range.Font.Bold, Range.Font.Italic, Range.Font.Color (returns an RGB value), Range.Font.Size, Range.Font.Name.
  • Fill: Range.Interior.Color (RGB), Range.Interior.Pattern (e.g., xlSolid, xlNone).
  • Borders: Each border side is a Border object: Range.Borders(xlEdgeLeft), xlEdgeTop, xlEdgeBottom, xlEdgeRight, and xlInsideVertical for interior lines. Each has .LineStyle (e.g., xlContinuous, xlNone) and .Weight (xlThin, xlMedium).
  • Alignment: Range.HorizontalAlignment (xlLeft, xlCenter, xlRight), Range.VerticalAlignment, Range.WrapText (True/False).

For example, to check if A1 has a red font:

If Range("A1").Font.Color = RGB(255, 0, 0) Then
    MsgBox "Red font"
End If

How to Choose the Right Property for Your Task

Decide what you need to know:

  • If you need to replicate the display format (e.g., to apply the same date format to another cell), use NumberFormat and assign it directly: Range("C2").NumberFormat = Range("A1").NumberFormat.
  • If you need to know if a cell is bold for conditional logic, check Font.Bold (returns True/False).
  • If you need to detect a filled background for highlighting, check Interior.Color and compare to RGB(255,255,255) (white) or xlNone (no fill).
  • If you need to copy all formatting from one range to another, use the Copy method with PasteSpecial xlPasteFormats, which is simpler than reading each property.

Common Problems and Misconceptions

Problem: Format returns “General” even though the cell shows a date. This happens when the cell is formatted as General but the value is a date serial number. Excel displays it as a date because of regional settings, but the format is actually General. To force a date format, you must assign a date format code.

Problem: Comparing colors fails. Interior.Color returns a long integer, not a simple RGB. Use RGB(r,g,b) to convert, but note that Excel’s color system uses a different order (BGR). For most comparisons, RGB(255,0,0) works because the conversion is symmetrical for pure colors.

Misconception: You can read a cell’s format as a single object. There is no “Format” object that contains everything. You must access each property separately. For a full snapshot, you could write a custom function that returns a dictionary of all properties, but that’s overkill for most tasks.

Practical Example: Copy Formatting Between Cells

Suppose you want to copy the number format and font boldness from A1 to B1, but not the value. Use this macro:

Sub CopyFormat()
    ' Save a copy of the workbook before running (macros can't be undone with Ctrl+Z)
    Dim src As Range, dst As Range
    Set src = Range("A1")
    Set dst = Range("B1")
    
    dst.NumberFormat = src.NumberFormat
    dst.Font.Bold = src.Font.Bold
    dst.Font.Color = src.Font.Color
    dst.Interior.Color = src.Interior.Color
End Sub

This is straightforward, but for complex formatting, better to use src.Copy and dst.PasteSpecial Paste:=xlPasteFormats.

Alternatives to Reading Format with VBA

If you need to inspect formatting without code, Excel has built-in tools:

  • Cell Styles: Home > Cell Styles shows named styles; you can see which style is applied to a selected cell.
  • Format Cells dialog: Press Ctrl+1 (Mac: Cmd+1) to see the full format settings of a selected cell.
  • Conditional Formatting: If the format is applied by conditional formatting, reading NumberFormat or Font.Color will return the default format, not the conditional one. To see the applied conditional format, you must evaluate the condition using Range.DisplayFormat (Excel 2010+). For example, Range("A1").DisplayFormat.Font.Color returns the color as actually displayed, including conditional formatting.

Using DisplayFormat is often more accurate for what the user sees, but it is slower and cannot be used in array formulas or on a UDF called from a worksheet cell.

Frequently Asked Questions

How do I get the date format of a cell in VBA?

Use Range("A1").NumberFormat and inspect the string. If it contains “yy” or “dd” or “mmm”, it’s likely a date format. For example, “dd-mmm-yy” returns a date. To be safe, check if the cell’s value is a date using IsDate(Range("A1").Value) and then read the format.

How do I check if a cell is formatted as currency?

Currency formats typically start with “$” or “#,##0.00” and may include a currency symbol. You can test if NumberFormat contains “$” or “[$-x]” (for locale-specific currency). For example, If InStr(Range("A1").NumberFormat, "$") > 0 Then.

How do I copy formatting from one cell to another in VBA?

The simplest method is Range("A1").Copy then Range("B1").PasteSpecial Paste:=xlPasteFormats. This copies all formatting including number format, font, fill, borders, and alignment. Alternatively, assign individual properties as shown above.

Why does my VBA code return “General” for a cell that shows a date?

Because the cell’s format is literally “General”, but Excel is displaying the date due to the cell’s value being a date serial number and the column width or regional settings. To display it as a date, you must explicitly set a date format like Range("A1").NumberFormat = "mm/dd/yyyy".

Can I read conditional formatting in VBA?

Yes, but not directly from the cell’s format properties. Use Range.DisplayFormat to get the effective format after conditional formatting is applied. For example, Range("A1").DisplayFormat.Font.Bold returns True if a conditional rule makes it bold. Note that DisplayFormat is read-only and cannot be used in worksheet functions.

How do I get the background color of a cell in VBA?

Use Range("A1").Interior.Color. It returns a long integer. To convert to RGB, use Dim r As Long, g As Long, b As Long: r = Color Mod 256: g = (Color \ 256) Mod 256: b = (Color \ 65536) Mod 256. Or simply compare with RGB(255,0,0) for red.

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.