We help IT Professionals succeed at work.

123 conditional cell formatting

BobArnett
BobArnett asked
on
Medium Priority
580 Views
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.)
Comment
Watch Question

Commented:
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"
     Else
          r.background.backcolor.colorname="white"
     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

Commented:
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.

Author

Commented:
cool! just what i wanted.

Author

Commented:
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?

Commented:
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.