Number format

The NumberFormat property sets the cell's number format similarly to the GUI by assigning a string with a valid format. Number formats have four parts, separated by semicolons. By default, the first is applied to positive numbers, the second to negative numbers, the third to zero, and the fourth to text. To assign a text format, use the @ string. In Visual Basic for Applications (VBA):

ActiveSheet.Range("A1").NumberFormat = "+0;-0;0;@"

In AppleScript, this becomes:

set number format of range "A1" of active sheet to "+0;-0;0;@"

You can also modify the application of the sections, and assign one of the eight custom font colors. For example, this format sets numbers less than negative 100 to red within parentheses, values between -100 and +100 (inclusive) to green, and values greater than 100 to red. The non-parenthesis formats have a space the width of a parenthesis added to the right side to make sure the numbers align. In VBA:

ActiveSheet.Range("A1:A100").NumberFormat = _
"[Red][<-100](0);[Green][<=100]0_);[Red]0_);@_)"

In AppleScript, this becomes:

set number format of range "A1:A100" of active sheet to ¬
	"[Red][<-100](0);[Green][<=100]0_);[Red]0_);@_)"

Align and wrap text

Alignment (namely left, center, and right for horizontal alignment) is set using the horizontal alignment and vertical alignment properties in AppleScript. Whether to wrap text is set using the wrap text property. In VBA:

With ActiveSheet.Range("A1:J10")
	.HorizontalAlignment = xlCenter
	.VerticalAlignment = xlCenter
	.WrapText = True
End with

In AppleScript, this becomes:

tell application "Microsoft Excel"
	tell range "A1:J10" of active sheet
		set horizontal alignment to horizontal align center
		set vertical alignment to vertical alignment center
		set wrap text to true
	end tell
end tell

Note that the enumeration for the horizontal alignment and vertical alignment parameters differ: horizontal align center and vertical alignment center.

Font format

Font formats are set using the cell's Font object. You can apply multiple properties at once. In VBA:

With ActiveSheet.Range("A1").Font
	.Name = "Verdana"
	.FontStyle = "Bold"
	.Size = 12
	.Strikethrough = False
	.Superscript = False
	.Subscript = False
	.OutlineFont = False
	.Shadow = False
	.Underline = xlUnderlineStyleDouble
	.ColorIndex = xlAutomatic
End With

In AppleScript, this becomes:

tell application "Microsoft Excel"
	tell font object of range "A1" of active sheet
		set {name, font style, font size, strikethrough, ¬
			superscript, subscript, outline font, ¬
			shadow, underline, font color index} to ¬
			{"Verdana", "Bold", 12, false, false, ¬
			false, false, false, underline style double, ¬
			color index automatic}
	end tell
end tell

This time, the code sets all properties at once in a list. It is easier to keep track of which property is set to which value if you do each on a separate line.

The color index property refers to the 56 colors that are available in the color palette. These colors are the same as the 56 colors that are available in the palette on the Format menu, rather than the reduced palette of 40 colors available on the Formatting Palette and toolbar.

But the colors are in a completely different order — the "default palette" used by VBA and AppleScript must date back quite a few years. The correct palette layout, with identifying index numbers, is discussed in the "Class: font" topic in the "Text Suite Classes" section of the Microsoft Excel 2004 AppleScript Reference (http://download.microsoft.com/download/3/A/7/3A70FB4B-0C6A-43E3-AAB7-AC9166B25632/Excel2004AppleScriptRef.pdf).

In addition to the eight custom colors that you can set using the number format property, you can use the font color index property to set the font color. In VBA:

ActiveSheet.Range("A1").Font.ColorIndex = 3 'Default palette red

In AppleScript, this becomes:

set font color index of font object of range "A1" of active sheet to 3

Take a good look at the color index enumeration for the font color index property of font class (Text Suite). It gives three so-called constants as available: color index automatic (that's generally black), color index none, and a color index integer, which will compile and run without error. However, it turns any existing color black, so it must be defaulting to color index automatic.

This is what is available of what could be 56 constants — namely the numbers 1 through 56. In VBA, if you check the Object Browser you'll see only xlAutomatic and xlNone listed, which have (as all xl and vb constants do) their own numeric version (large negative numbers). But numbers 1 through 56 have also been reserved.

In AppleScript, a color index integer is a placeholder constant for the numbers 1 through 56. It's really just a description, not a constant.

You can set a Microsoft RGB value for the color, but Microsoft Excel will choose the closest color available in the palette. It's not exact, but it uses pure red, green, and blue accurately. In VBA:

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

In AppleScript, this becomes:

set color of font object of range "A1" of active sheet to {255, 0, 0}