Assigning Formats by Formula
The STYLE() function can be added to an existing formula in a cell. For example, together with the CURRENT function, you can colour a cell depending on its value. The formula =... + STYLE(IF(CURRENT() > 3; "Red"; "Green")) applies the cell style "Red" to cells if the value is greater than 3, otherwise the cell style "Green" is applied.
If you would like to apply a formula to all cells in a selected area, you can use the Find & Replace dialogue box.
-
Select all the desired cells.
-
Select the menu command Edit - Find & Replace.
-
For the Find term, enter: .*
".*" is a regular expression that designates the contents of the current cell.
- Enter the following formula in the Replace field: =&+STYLE(IF(CURRENT()>3;"Red";"Green"))
The "&" symbol designates the current contents of the Find field. The line must begin with an equal sign, since it is a formula. It is assumed that the cell styles "Red" and "Green" already exist.
- Mark the fields Regular expressions and Current selection only. Click Find All.
All cells with contents that were included in the selection are now highlighted.
- Click Replace all.