We help IT Professionals succeed at work.

123 conditional cell formatting

BobArnett asked
Medium Priority
Last Modified: 2010-05-18
I am using SmartSuite ME and would like to know if there is a way to conditionally format a specific cell. (i.e. if the resulting formula value is greater than 1000, display it in bold green.)
Watch Question

This can be done with a Lotus Script.

Here are the steps for an example that will make the cell background light yellow when the number 1 is typed in the cell and make it White if anything else is typed in the cell

Highlight the range you want conditionally formatted.
say for example A1 thru A10 on Sheet A

Goto Edit, Show Script Editor

When the dialog comes up, the "Object" dropdown box should display your range (A:A1...A:A10)
In the "Script" dropdown to the right of the "Object" dropdown it should say "(Declarations)", change this to CellContentsChange

Once you do this it will show two lines

Sub Cellcontentschange(Source As Range)

End Sub

Paste in the following code between the Sub and End Sub Lines    
Forall  r In Source.cells
     If r.Contents = "1" Then
          r.Background.BackColor.ColorName= "light yellow"
     End If
End Forall

Now Save the script, close the script editor.

Now type in some values in any of the cells from a1 thru a10. If you type a 1 in the cell it will change to yellow background color.

This is the basic idea, what you want to do with it will be up to you

Note that the .Contents property returns a string value, so you will have to evaluate as sstring or use one of the convert functions to convert to a long or double to test.


cool! just what i wanted.


Just an add on question. 123 Script Help doesn't have any information on this nor does the LotusScript(3) book I have.Can you direct me to where I can get this information with color names etc?

In this case to get the color names I recorded a script and edited it to find out the values. Unfortunatly there is not alot of documentation on Lotus Script.