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 74

     tell application "Microsoft Excel"

          tell thisBorder

              set line style to continuous

              set weight to border weight thick

              set color index to 3

          end tell

     end tell

end Customize

You will note that it is possible to send an application object out to an external handler as a parameter even though that takes it outside the Excel application block. The handler can still accept the item (which it will know as border id border edge left of cell "D10" of active sheet of application "Microsoft Excel") to pass back into the application tell block within the handler when called.

Conditional Formatting

Conditional formatting is applied using the format condition object in the Excel Suite. You will note that it is an element of range class, which may have none, or several of them. (It seems to be limited here to three per range.) Each conditional format may have at most three conditions, and the formats that can be set are for font, interior (background) and border. For instance, in VBA, this will apply a bold green font to values between 0 and 10:

With ActiveSheet.Range("B1").FormatConditions

   .Delete 'Delete existing conditional formats

   With .Add(Type:=xlCellValue, _

             Operator:=xlBetween, _

             Formula1:="0", _

             Formula2:="10").Font

      .Bold = True

      .Italic = False

      .ColorIndex = 4

   End With

End With

Here is the AppleScript version, which works delightfully once you get the syntax right. It is quite interesting to study:

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:cell value, condition operator:¬

                   operator between, formula1:"0", formula2:"10"}

          tell font object of newFormatCondition

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

          end tell

     end tell

end tell

Format conditions are elements, not properties, of range, so you can delete any existing ones before making new ones, a good idea when you are making your first format condition for a range in a workbook that may have passed through other hands or has been around. (If you go on to make another format condition at the same range for different conditions, obviously omit the delete command if you want to keep the ones you've just made.) Put the delete command in a try block because it will error if there are no existing format conditions.