Formatting Cells with VBA

Cell formatting means changing the appearance or display of data in an Excel cell.

Examples include:

• Changing font style
• Changing font size
• Changing font color
• Changing background color
• Adding borders
• Changing alignment
• Changing number format

A cell can be formatted directly using VBA.

Range("A1").Font.Bold = True

This makes the text in A1 bold.

No Select or Activate operation is required.

Use the Bold property to make text bold.

Range("A1").Font.Bold = True

To remove bold formatting:

Range("A1").Font.Bold = False

The Italic property makes text italic.

Range("A1").Font.Italic = True

To remove italic formatting:

Range("A1").Font.Italic = False

The Size property changes the font size.

Range("A1").Font.Size = 16

This changes the font size of A1 to 16.

The Name property changes the font family.

Range("A1").Font.Name = "Arial"

This changes the font of A1 to Arial.

The Color property can be used to change the font color.

Range("A1").Font.Color = vbRed

This changes the font color to red.

You can also use RGB values:

Range("A1").Font.Color = RGB(0, 0, 255)

This sets the font color to blue.

The Interior object is used to format the cell background.

Range("A1").Interior.Color = vbYellow

This changes the background of A1 to yellow.

RGB can be used to create custom colors.

Range("A1").Interior.Color = RGB(0, 112, 192)

The RGB function uses three values representing red, green, and blue.

RGB(red, green, blue)

The HorizontalAlignment property controls horizontal alignment.

Range("A1").HorizontalAlignment = xlCenter

This centers the content horizontally.

Other common values include:

xlLeft
xlCenter
xlRight

The VerticalAlignment property controls vertical alignment.

Range("A1").VerticalAlignment = xlCenter

This centers the content vertically inside the cell.

Borders can be added to cells using the Borders collection.

Range("A1:C5").Borders.LineStyle = xlContinuous

This applies borders to the range A1:C5.

The Weight property can change the thickness of a border.

Range("A1:C5").Borders.Weight = xlThin

Other commonly used border weights include:

xlThin
xlMedium
xlThick

You can also change the border color.

Range("A1:C5").Borders.Color = vbBlack

This applies a black border color to the range.

The NumberFormat property controls how numbers are displayed.

Range("A1").NumberFormat = "0.00"

This displays the number with two decimal places.

A cell can be formatted as currency.

Range("A1").NumberFormat = "₹#,##0.00"

This displays the value using an Indian rupee format.

The percentage format can be applied using NumberFormat.

Range("A1").NumberFormat = "0.00%"

For example, a value of 0.85 can be displayed as 85.00%.

Dates can be displayed using a specific format.

Range("A1").NumberFormat = "dd-mm-yyyy"

This displays the date in day-month-year format.

The WrapText property allows long text to appear on multiple lines.

Range("A1").WrapText = True

This wraps the text inside A1.

You can adjust the size of rows and columns while formatting a worksheet.

Rows(1).RowHeight = 25

Columns("A").ColumnWidth = 20

This changes the height of row 1 and width of column A.

The same formatting can be applied to multiple cells at once.

Range("A1:D10").Font.Name = "Arial"
Range("A1:D10").Font.Size = 11
Range("A1:D10").Borders.LineStyle = xlContinuous

This formats the complete range A1:D10.

A common use of VBA formatting is to format report headings.

With Range("A1:D1")

    .Font.Bold = True
    .Font.Size = 12
    .Font.Color = vbWhite
    .Interior.Color = RGB(0, 112, 192)
    .HorizontalAlignment = xlCenter
    .Borders.LineStyle = xlContinuous

End With

The With block allows several formatting properties to be applied to the same range.

VBA can format cells based on their values.

If Range("B2").Value < 40 Then

    Range("B2").Interior.Color = vbRed

End If

If the value in B2 is below 40, its background becomes red.

You can automatically format Pass and Fail results.

If Range("C2").Value = "Pass" Then

    Range("C2").Interior.Color = vbGreen
    Range("C2").Font.Color = vbWhite

Else

    Range("C2").Interior.Color = vbRed
    Range("C2").Font.Color = vbWhite

End If

The following example formats a simple student marksheet.

Sub FormatMarksheet()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Students")

    With ws.Range("A1:D1")

        .Font.Bold = True
        .Font.Size = 12
        .Font.Color = vbWhite
        .Interior.Color = RGB(0, 112, 192)
        .HorizontalAlignment = xlCenter
        .Borders.LineStyle = xlContinuous

    End With

    With ws.Range("A2:D10")

        .Borders.LineStyle = xlContinuous

    End With

    ws.Columns("A:D").AutoFit

End Sub

• Applying formatting to the wrong range.
• Using the wrong RGB values.
• Forgetting to specify the worksheet.
• Changing number formats incorrectly.
• Applying formatting to an entire column unnecessarily.
• Using Select when direct formatting is possible.
• Overwriting existing formatting without checking the worksheet.

The following procedure prepares a simple report automatically.

Sub FormatReport()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Reports")

    With ws.Range("A1:E1")

        .Font.Bold = True
        .Font.Size = 14
        .Font.Color = vbWhite
        .Interior.Color = RGB(31, 78, 121)
        .HorizontalAlignment = xlCenter
        .Borders.LineStyle = xlContinuous

    End With

    With ws.Range("A2:E20")

        .Borders.LineStyle = xlContinuous
        .VerticalAlignment = xlCenter

    End With

    ws.Columns("A:E").AutoFit

End Sub

• Use With blocks when applying several properties to the same range.
• Specify the worksheet for important formatting operations.
• Format only the required range.
• Use meaningful number formats.
• Use consistent formatting for reports.
• Avoid unnecessary Select and Activate operations.
• Test formatting macros on sample data first.
• Keep data formatting separate from data-processing logic when possible.

A typical VBA formatting workflow is:

1. Identify the worksheet.
2. Identify the required cell or range.
3. Choose the formatting properties.
4. Apply font formatting.
5. Apply background and border formatting.
6. Apply alignment and number formatting.
7. Adjust row height or column width if required.
8. Check the final worksheet.

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Students")

With ws.Range("A1:D10")

    .Font.Name = "Arial"
    .Borders.LineStyle = xlContinuous
    .VerticalAlignment = xlCenter

End With

ws.Columns("A:D").AutoFit

VBA provides many properties for automatically formatting Excel cells. The most commonly used properties include Font, Interior, Borders, Alignment, and NumberFormat.

For example:

With Range("A1:C5")

    .Font.Bold = True
    .Font.Size = 12
    .Interior.Color = vbYellow
    .Borders.LineStyle = xlContinuous
    .HorizontalAlignment = xlCenter

End With

This applies several formatting properties to the complete range.

Formatting can also be combined with conditions:

If Range("C2").Value = "Pass" Then

    Range("C2").Interior.Color = vbGreen

Else

    Range("C2").Interior.Color = vbRed

End If

These techniques are useful for creating professional marksheets, student reports, invoices, dashboards, attendance sheets, and other automated Excel documents.