Moving from Microsoft Office VBA to AppleScript:
MacTech's Guide to Making the Transition

Page Prev and Page Next buttons at bottom of the page.

April, 2007
Page 75

Now you will see from the dictionary that every property of format condition is listed as read-only, so how can we set anything? It so happens that there are many classes, at least in other applications, with properties listed in the dictionary as read-only but which can be set at inception, when using make new with properties {…} only, and never afterwards. It is quite common to find these in other applications.

There do not seem to be many such classes in Excel or Word or PowerPoint. However, many "more standard" scriptable applications, such as Entourage, are full of such classes. It is always worth checking and testing for these.

The properties here that can be set at inception are the enumerations and text properties: format condition type, condition operator, formula1 and formula2, i.e., the conditions. But if you try to include settings for the font object or interior object within the properties for make new format condition, they will be ignored as always. Instead, although you can't "set" a font object itself, you can set its own properties (bold, italic, name, font color index, etc.) after the format condition element in which the font object resides is made. This is quite normal for the Office applications: it is true for all the complex objects such as fonts that other objects can have as their properties.

What is unusual, and pleasing, is that the "simple" read-only properties which are not objects and don't have properties of their own that can be set afterwards, can be set at inception, as they should be. This has been implemented properly, and makes these format condition objects useful and very scriptable. The Excel developers are to be commended.

One other thing to note is that these are elements that must be made at beginning or at end of the range – it doesn't matter which (except perhaps to primacy if you overlap two conflicting conditions). You may also have noticed that the dictionary lists "formula 1" and "formula 2" as the parameter names but they compile to "formula1" and "formula2" without spaces, just as we saw them in add data validation. That means they've been implemented as synonyms: you can type either, which is nice, and both versions compile.

You can apply more complex formats based on formula conditions by declaring the Type to be expression (xlExpression in VBA), meaning a formula.

With ActiveSheet.Range("B1").FormatConditions

   .Delete

   With .Add(Type:=xlExpression, _

             Formula1:="=AND($A1>0,$A1<10)")

      With .Font

          .Bold = True

          .Italic = False

          .ColorIndex = 2

      End With

      .Interior.ColorIndex = 3

   End With

End With

tell application "Microsoft Excel"

     tell range "B1" of active sheet

          try

              delete (every format condition)

          end try

          set newFormatCondition to make new format condition at end ¬

              with properties {format condition type:expression, formula1:¬

              "=AND($A1>0,$A1<10)"}

          tell newFormatCondition

              tell its font object

                   set {bold, italic, font color index} to {true, false, 2}

              end tell

              set color index of its interior object to 3

          end tell