Font colour in cell table

To evaluate the string expression contained in the .Text property of the CellTableCell object one may use the FPScript function Execute(). In the following macro this formula is generated on the fly and deleted after execution.

[code]
Option Explicit

Public Sub ColorizeCells()
Dim myTable As CellTable
Set myTable = ActiveDatabase.ActiveFolder.Object(“Zellentabelle”, _
fpObjectTypeCellTable)

Dim oEvaluator As Formula
Set oEvaluator = ActiveDatabase.ActiveFolder.Add(“Eval”, fpObjectTypeFormula, _
fpNameClashHandlingAutoRename)
oEvaluator.Formula = “Arguments str” & vbCrLf & “Execute(str)”

Dim nRows As Integer, nCols As Integer
nRows = myTable.Cells.NumberOfRows
nCols = myTable.Cells.NumberOfColumns

Dim i As Integer
Dim res As Double
For i = 1 To 3
‘ cells are numbered row-wise so 2 + i * nCols enumerates the cells
‘ from the second column starting at the second row
With myTable.Cells(2 + i * nCols).Text
res = oEvaluator.Call(ExtractExpression(.Text))
If res < -1 Then
.Font.Color = vbBlue
ElseIf res <= 1 Then
.Font.Color = vbBlack
Else
.Font.Color = vbRed
End If
End With
Next i

oEvaluator.Delete
End Sub

Private Function ExtractExpression(str As String)
str = Mid$(str, InStr(str, "{") + 1)
ExtractExpression = Left$(str, InStrRev(str, "}") – 1)
End Function
[/code]

This macro assumes a cell table named "Zellentabelle" with atleast two columns and four rows. It scans the 3 cells in the second column starting from the second row and changes the font color to blue for values less than -1, red for values greater than +1, values between this limits are colored black.

The automatic coloring of table cells depending on their value is indeed planned for FlexPro 10.
To transfer a FlexPro table to Excel, copy and paste via the ordinary clipboard mechanism can be used: Select the whole table (using Ctrl-A) and use Ctrl-C followed by Ctrl-V in the destination Excel table to insert the content.