.. highlightlang:: us

.. index:: y^edit-conditionalformatting

.. _edit_conditional_formatting:

Edit=>Conditional Formatting
----------------------------

Conditional formatting can be used to change the text color and fill color
of a table cell depending on the cell's value, or to create a rule with a 
formula.

To add a new conditional formatting rule to a table: 

Execute the command:

*  Select the cell range to apply the cell formatting,

*  Right click on the selected cell range and choose 
   Conditional Formatting=>Add Cell Rule

.. image:: S:/uniplot-obj/images/TableConditionalFormat_Add-en.*

.. us.dialog

.. us.dlgitem

**Cell Reference**

   Specifies the cells reference in the A1-Style (column letter and row number).
   It refers to a single cell, for example B2 or a ranges of cells, for example
   A1:C4. The cell reference specifies the cells to which the format is applied.

.. us.dlgitem

**Format all cells based on their values**

   If enabled, the cell value will be compared to the specified value
   **Value1**. The following is of operators that can be used:
      
      .. list-table::
         :header-rows: 1

         * - Operator
         * - greater as (>)
         * - greater or equal (>=)
         * - less as (<)
         * - less or equal (<=)
         * - between  (>= v1 && < v2)
         * - not between (< v1 || > v2)
         * - equal (==)
         * - not equal (!=)

.. us.dlgitem

**Value1**
   Is a real value.

.. us.dlgitem

**Value2**
   Is a real value. Will only be used with ``between`` and ``not between``.

.. us.dlgitem

**Format all cells based on the formula expression**

.. us.dlgitem

**Expression**

   In an expression the following operators can be used:
   ``>``, ``>=``, ``<``, ``>=``, ``==``, ``!=``. The expressions can be
   AND (``&&``) and  OR (``||``) connected. 

   Liste mit Beispielen:

      .. list-table::
         :header-rows: 1

         * - Expression
           - Description
         * - ``A1 > 10``
           - If the value in cell A1 is greater as 10, the expression is TRUE (1).
         * - ``B2 > C2``
           - If the value in B2 is greater than the value in C2, the expression is TRUE (1).
         * - ``sum(B2:E2) <= 10`` 
           - If the sum of the cells in the range B2 to E2 is smaller or equal to 10, the expression is TRUE (1).
         * - ``A1 > B1 && A1 < C2``
           - If the value in cell A1 is between the values of B1 and C2, the expression is TRUE (1).

.. us.dlgitem

**Fill Color**

  Color ist used to fill the cell.

.. us.dlgitem

** Font Color**

  Color is used to display the cell value.

Condional Format Dialogbox
--------------------------

The following dialog box can be used to add, remove or edit cell rules.

.. image:: S:/uniplot-obj/images/TableConditionalFormat_Manage-en.*

.. us.dialog

.. us.dlgitem

**List of cell rules.**

   Each row is a format rule. The rules are evaluated as shown 
   in the list.

UniScript-Interface
-------------------

The **TableFormatCondition_AddFormat** function can be used in UniScript
to add conditional formatting to a table. The format parameter is an object
Example::

   oFormat = [. sqref = "a1:a2",  
                type = "cellvalue", 
                formula = ["less", "0"], 
                format = "fill-color = 'cyan' font-color = 'red'"];
   TableFormatCondition_AddFormat(hTable, oFormat)

The color can also be specified as an RGB value, as follows::

   oFormat.format = "fill-color = 'rgb(255,0,0)' font-color = 'rgb(100,100,100)'";

Operator names: greater, between, notbetween, greaterequal, less, lessequal,
equal, notequal.

More examples::

   oFormat = [. sqref = "a1", type = "cellvalue",  formula = ["between", "10", "20"], format = fill-color='rgb(255,0,0)'];
   oFormat = [. sqref = "a1", type = "cellvalue",  formula = ["between", "10", "20"], format = fill-color='rgb(255,0,0)'];
   oFormat = [. sqref = "C4:G4",  type = "expression", formula = "(C4 < D4)",  format = fill-color='green'];
   oFormat = [. sqref = "D9:G13", type = "expression", formula = "(D9/1000 > 0.46)", format = fill-color='rgb(255,0,0)'];

.. us.history

**History**

.. list-table::
   :header-rows: 1

   * - Version
     - Description
   * - R2018.6
     - New.

.. seealso::
   :ref:`edit-objecttable-configuration`,
   :ref:`edit-objecttable`

:sub:`id-1237658`