You can use the following basic syntax in VBA to format each cell in a range as a percentage.

Sub FormatPercent()
    Dim i As Integer

    For i = 2 To 11
    Range("A" & i).NumberFormat = "0.00%"
    Next i
    
End Sub

This particular example formats each cell in the range A2:A11 as a percentage value with two decimal places.

The following example shows how to use this syntax in practice.

Example: Format Cells as Percentage Using VBA

Suppose we have the following list of values in Excel:

Suppose that we would like to format each value as a percentage.

For example:

• 0.22 should be formatted as 22%.
• 0.35 should be formatted as 35%.

And so on.

We can create the following macro to do so:

Sub FormatPercent()
    Dim i As Integer

    For i = 2 To 11
    Range("A" & i).NumberFormat = "0.00%"
    Next i
    
End Sub

When we run this macro, we receive the following output:

Each value is now formatted as a percentage with two decimal places.

Note that the number of zeros after the decimal point in the NumberFormat property specifies the number of decimal places to display in each percentage.

We could use the following syntax to display no decimal places in each percentage:

Sub FormatPercent()
    Dim i As Integer

    For i = 2 To 11
    Range("A" & i).NumberFormat = "0%"
    Next i
    
End Sub

When we run this macro, we receive the following output:

Notice that the decimal places have been removed from each percentage value.

Note: You can find the complete documentation for the VBA NumberFormat property here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Sort Sheet by Multiple Columns
VBA: How to Count Number of Rows in Range
VBA: How to Filter a Column