Code & Hacks by Mark Grimes

I recently switched to a Mac and really miss my auto color cells VBA script for Excel. After quite a bit of digging (and trial-and-error) I managed to recreate the functionality using AppleScript. It is pretty slow, but it works!

Just like the VBA version, this automatically color codes cells to help identify inputs, formulas, etc. For example, cells that contain only numbers are colored blue, all formulas black, references to other workbooks are green and cells that include the =OFFSET() function (what I use for setting up different scenarios) are rust.

Open up the AppleScript Editor, paste the following code and save it as /Users/<your-username>/Documents/Microsoft User Data/Excel Script Menu Items/AutoColorCells\scc.scpt. The \scc in the filename creates a keyboard shortcut control-shift-c.

tell application "Microsoft Excel"
    --activate
    set colStart to (get first column index of selection)
    set rowStart to (get first row index of selection)
    set colCount to (get count columns of selection)
    set rowCount to (get count rows of selection)

    set status bar to "Auto coloring the cells"
    set screen updating to false

    repeat with i from rowStart to rowStart + rowCount - 1
        set status bar to "Row " & i - rowStart + 1 & " of " & rowCount
        repeat with j from colStart to colStart + colCount - 1
            --set theCell to cell j of row i
            --set f2 to (get formula of theCell)
            set f to (get formula of cell j of row i)

            if f starts with "=" then
                if f contains ".xls" then
                    set font color index of font object of cell j of row i to 10 -- rust
                else if f contains "OFFSET" then
                    set font color index of font object of cell j of row i to 9 -- green
                else if my isallnumbers(f) then
                    set font color index of font object of cell j of row i to 5 -- blue
                else
                    set font color index of font object of cell j of row i to 1 -- auto
                end if
            else
                if my isallnumbers(f) then
                    set font color index of font object of cell j of row i to 5 -- blue
                else
                    set font color index of font object of cell j of row i to 0 -- auto
                end if
            end if
        end repeat
    end repeat

    set screen updating to true
    set status bar to "Auto Color Done"

end tell

to isallnumbers(f)
    set l to length of f

    --log_event("f is " & f) of my commonScript

    repeat with k from 1 to l
        set c to ASCII number of character k of f
        if c < 40 or c > 61 then
            return false
        end if
    end repeat
    return true
end isallnumbers

Rather than merging cells to center headers, I prefer to have text centered across selection. This avoids problems with deleting and filling columns that are cosed by merged cells. The following sets up a command to toggle centering across columns.

Open up the AppleScript Editor, paste the following code and save it as /Users/<your-username>/Documents/Microsoft User Data/Excel Script Menu Items/AlignCentered\sca.scpt. The \sca in the filename creates a keyboard shortcut control-shift-a.

-- Align selected cells across selection
-- Copyright under GPL by Mark Grimes
-- Saving with '\sca' in the filename creates Shortcut: Crtl+Shift+a

tell application "Microsoft Excel"
    --activate
    tell range (get address selection) of active sheet
        if (get count columns) > 1 or (get count rows) > 1 then
            if (get horizontal alignment) is horizontal align center across selection then
                set horizontal alignment to horizontal align general
            else
                set horizontal alignment to horizontal align center across selection
            end if
        else
            if (get horizontal alignment) is horizontal align center then
                set horizontal alignment to horizontal align general
            else
                set horizontal alignment to horizontal align center
            end if
        end if
    end tell
end tell

Here is another AppleScript version of a [excel.html#backup](prior vba) script. This one creates a back of the current workbook. It copies the last saved version of the current workbook to a Backup subdirectory below the directory in which that file was saved. It adds a counter (ie, .001) before the .xls(x) extension.

-- Save a backup of the current file
-- Copyright under GPL by Mark Grimes
-- Saving with '\scs' in the filename creates Shortcut: Crtl+Shift+S

tell application "Microsoft Excel"
    set macPath to get full name of (get properties of active workbook)
    set curPath to my posix_path(macPath)
    set cmdStatus to do shell script ¬
        "perl -MFile::Copy -MFile::Basename -MFile::Spec -e' " & ¬
        "$f=qq{" & curPath & "}; " & ¬
        "$d = File::Spec->catdir(dirname($f),q{Backup});" & ¬
        "mkdir $d unless -d $d;" & ¬
        "$b = File::Spec->catfile($d,basename($f)); " & ¬
        "$i=1;" & ¬
        "do {$s = sprintf( qq{%03d}, $i++); $b=~s/(?:.\\d{3})?.(xlsx?)$/.$s.$1/; }" & ¬
        "  while( -e $b );" & ¬
        "copy $f, $b or die qq{Error copying $f to $b: $!};" & ¬
        "print qq{Backed up last saved version to: $b};" & ¬
        " ' "
    display dialog cmdStatus
end tell

-- From: http://www.macosxhints.com/article.php?story=20011030193449870
-- Thanks!
on posix_path(mac_path)
    set mac_path to (mac_path as text)
    set root to (offset of ":" in mac_path)
    set rootdisk to (characters 1 thru (root - 1) of mac_path)
    tell application "Finder"
        if (disk (rootdisk as string) is the startup disk) then
            set unixpath to "/" & (characters (root + 1) thru end of mac_path)
        else
            set unixpath to "/Volumes:" & mac_path
        end if
    end tell
    set chars to every character of unixpath
    repeat with i from 2 to length of chars
        if item i of chars as text is equal to "/" then
            set item i of chars to ":"
        else if item i of chars as text is equal to ":" then
            set item i of chars to "/"
        else if item i of chars as text is equal to "'" then
            set item i of chars to "\\'"
        else if item i of chars as text is equal to "\"" then
            set item i of chars to "\\" & "\""
        else if item i of chars as text is equal to "*" then
            set item i of chars to "\\*"
        else if item i of chars as text is equal to "?" then
            set item i of chars to "\\?"
        else if item i of chars as text is equal to " " then
            set item i of chars to "\\ "
        else if item i of chars as text is equal to "\\" then
            set item i of chars to "\\\\"
        end if
    end repeat
    return every item of chars as string
end posix_path