Note: the values in some cells simultaneously corresponds to two or more conditions. Likewise, set the second and third formatting conditions: Fill in the formatting parameters for the first condition. Method: Go to the menu of the «Conditional formatting» tool and select «New Rule».In the same way, set the yellow fill color for numbers greater than 20. Set the formatting condition as «Format cells that are GREATER THAN:» 10, and choose «Green Fill Dark Green Text». In the right one, select «Light Red Fill Dark Red Text».

«Highlight Cells Rules» - «Greater Than». We need the numbers that are greater than 6 to be highlighted with red. Conditional formatting – several conditions In the newly-opened window, you can see which rule is applied to which range.

Open the tool's menu click «Manage Rules».

Select the first cell in the range to which conditional formatting is applied.

You can do the following to check the accuracy of the established condition: Pay attention to this nuance in order to ensure the «Conditional formatting» tool performs the task properly. And the program would compare B1 with A11. If we selected the column from the bottom upwards rather than from top to bottom, the A11 cell would be the active one. Consequently, Excel compares the value in the A1 cell with the B1 value. In our example, the A1 cell was active at the moment we opened the tool. Since it's the active cell to which the reference in the condition is “tied.” Important note! When you use relative references, you have to pay attention to which cell was active the moment you opened the «Conditional formatting» tool. The coinciding values have been highlighted with a fill color. The program has compared every value in the A column with the corresponding value in the B column. The link should be Mixed or Relative! And not absolute!

In the left box, enter the link to the B1 cell. Click «Conditional formatting» - «Highlight Cells Rules» - «Equal To».

We will compare the column А1:А11 with the column В1:В11. Let's set the following formatting conditions: compare the values of cells in different ranges and highlight the same values. The values in the А1:А11 range that are less than the value of the B2 cell are filled with the selected color. The formatting result is plain to see in the Excel sheet immediately. In the left box, enter the link to the B2 cell (click on this cell and its name will appear in the box automatically). In this example, let's apply the condition «less» («Highlight Cells Rules» - «Less Than»). Select the initial range and open the window of the «Conditional formatting» tool. Let's compare the values in the range А1:А11 with the number in the B2 cell.

Openoffice conditional formatting range