SOME USEFUL RANGE OBJECT PROPERTIES

A Range object has dozens of properties. For complete details, consult the Help system in VBE.

Some Range properties are read-only properties, which means you can read but cannot change them. For example, every Range object has an Address property (which holds the range's address). You can access this read-only property by you can't change it.

The Value property

The Value property represents the value contained in a cell. It's a read-write property, so your VBA code can either read or change the value.

The following statement displays a message box that shows the value in cell A1 on Sheet1:
    MsgBox Worksheets("Sheet1").Range("A1").Value
It stands to reason that you would read the Value property only for a single-cell range object. For example, the following statement generates an error:
    MsgBox Worksheets("Sheet1").Range("A1:C3").Value
You can, however, change the Value property for a range of any size. The following statement enters the number 32 into each cell in a range:
    Worksheets("Sheet1").Range("A1:C3").Value = 32
Value is the default property for a Range object. That means that if you omit a property for a Range, VBA uses its value property. The following statements both enter a value of 75 into cell A1 on Sheet1:
    Worksheets("Sheet1").Range("A1").Value = 75
    Worksheets("Sheet1").Range("A1") = 75

The Text property

The Text property returns a string the represents the text as displayed in a cell - the formatted value.

The Text property is a read-only property.BR>
For example if cell A1 contains the value 12.3 and is formatted to display two decimals and a dollar sign ($12.30), then the following statement displays a message box containing $12.30: 
    MsgBox Worksheets("Sheet1").Range("A1").Text
But the following statement display a message box containing 12.3 (its value): 
    MsgBox Worksheets("Sheet1").Range("A1").Value

The Count property

The Count property returns the number of cells in a Range (all cells, not just the non-blank cells). It is a read-only property.

The following statement accesses a range's Count property and displays the result of 9 in a message box:
    MsgBox Range("A1:C3").Count
because there are 9 cells in that range.

The Column and Row properties

The Column property returns the column number of a single-cell range, the Row property returns the row number of a single-cell range.

For example, the following statement displays 6 because the cell is in the sixth column:
    MsgBox Worksheets("Sheet1").Range("F3").Column
This next expression displays 3 because cell F3 is in the third row:
    MsgBox Worksheets("Sheet1").Range("F3").Row
If the Range object consists of more than one cell, the Column property returns the column number of the first column in the range, and the Row property returns the row number of the first row in the range.

Don't confuse the Column and Row properties with the Columns and Rows properties. The column and Row properties return a single value. Columns and Rows properties return a Range object.

The Address property

Address, a read-only property, displays the cell address for a Range object in absolute notation.

The following statement displays $A$1:$E$5 in the message box:
    MsgBox Range(Cells(1,1),Cells(5,5)).Address

The HasFormula Property property

The HasFormula property (which is read-only) returns True if the single-cell Range object contains a formula, otherwise it returns False.

If the range consists of more than one cell, VBA returns True only if all cells in the range contain a formula, or False if all cells in the range don't have a formula. The property returns a Null if there is a mixture of formulas and non-formulas.

Be careful with the type of variables you use to maintain the results returned by the HasFormula property. When working with any property that returns a Null, it is easy to generate errors by using the wrong data type.

For example, assume that cell A1 contains a value and cell A2 contains a formula. The following statements generate an error because the range doesn't consist of all formulas or non-formulas: 
    Dim FormulaTest As Boolean
    FormulaTest = Range("A1:A2").HasFormula
To fix this type of situation, you should declare the FormulaTest variable as Variant rather than Boolean, as shown below:
    Dim FormulaTest As Variant
    FormulaTest = Range("A1:A2").HasFormula
    If TypeName(FormulaTest) = "Null" Then MsgBox "Mixed!"

The Font property

A Range object's Font property returns a Font object.

A Font object has many accessible properties. To change some aspect of a range's font, you must first access the range's Font object and then manipulate the properties of that object.
The following expression returns a Font object for a range:
   
    Range("A1").Font
The following statement sets to True the Bold property of the Font object contained in the Range object. This makes the value in the cell display in boldface:
    Range("A1").Font.Bold = True

The Interior property

A Range object's Interior property returns an Interior object.

For example, the following statement set to 3 the ColorIndex property of the Interior object contained in the Range object:
    Range("A1").Interior.ColorIndex = 3
and changes the cell's background color to red.

The ColorIndex values correspond to the color palette Excel currently uses, and take on any value from 1 to 56.

If you need to use standard colors, use the Color property instead of the ColorIndex along with a built-in constant: vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan, or vbWhite. For example the following statement makes cell A1 yellow:
    Range("A1").Interior.Color = vbYellow

The Formula property

The Formula property represents the formula in a cell. This is a read-write property, so you can access it to insert a formula into a cell. For example, the following statement enters a SUM formula into cell A13:
    Range("A13").Formula = "=SUM(A1:A12)"
Notice that the formula is a text string and is enclosed in quotation marks.

If you are using the Formula property to determine the formula already in a cell and the cell doesn't have a formula, the Formula property returns the cell's Value property.

The NumberFormat property

The NumberFormat property represents the number format (expressed as a text string) of the Range object.

It is a read-write property so your VBA code can change the number format of a cell. For example, the following statement changes the number format of column A to percent with two decimal places:
    Columns("A:A").NumberFormat = "0.00%"
Follow these steps to see a list of other number formats:
  1. Activate a worksheet.
  2. Access the Format Cells dialog box by pressing Ctrl-l.
  3. Click the Number tab.
  4. Select the Custom category to view some additional number format strings.