You use Highlight Cells Rules to highlight any cells that you have set the rule. In Highlight Cells Rules, you can use the following criteria:
- Greater Than
- Less Than
- Equal To
- Text that Contains
- A Date Occurring
- Duplicate Values and other rules.
To use Highlight Cells Rules, do the following:
1. Select the cells that you want to format.
2. On the Home tab, in the Styles, click Conditional Formatting, then point to Highlight Cells Rules, and then click the condition that you want.
For example, in the following range of cells, highlight students who get scores that are greater than 50.
To do this task, follow the procedure below:
1. Select the range of cells in column D (D2:D8).
2. On the Home tab, in the Styles, click Conditional Formatting, then point to Highlight Cells Rules, and then click Greater Than.
3. In the Greater Than dialog box, under Format cells that are GREATER THAN, type 50 in the box, and then click drop-down arrow of the box next to the box that you just type the value to select the format for the cell criteria (e.g. you select Light Red Fill).
Note: If you want to define the cell format other than the predefined cell format, click Custom Format, and then, in the Format Cells dialog box, select the format that you want.
4. Click Ok button.
The result will be as the figure below:
Another example, by using the range of cells above, highlight male students who get scores that are greater than or equal to 55. To do this task, do the following:
1. Select the range of cells (A2:D8) that you want to format.
2. On the Home tab, in the Styles, click Conditional Formatting, then point to Highlight Cells Rules, and then click More Rules.
Or on the Home tab, in the Styles, click Conditional Formatting, and then click New Rule.
The New Formatting Rule dialog box is displayed.
3. In the New Formatting Rule dialog box, select the last option: Use a formula to determine which cells to format.
4. Under Format values where this formula is true, enter the following formula:
5. Click Format button to format the cells. For example, you fill the cells background with pink color.
6. In the Format Cells dialog box, click Fill tab, and then select pink color on the color palette. If you cannot find this color on the color palette, click More Colors button, then in the Color dialog box, select your color, and then click Ok button three times.
See the result:
Note: If you want to create new rule, edit or delete existing rule, and view all conditional formatting rules in the workbook, click the cell that is formatted, and then on the Home tab, in the Style group, click Conditional Formatting, and then click Manage Rules. In the Conditional Formatting Rules Manager dialog box, you can do what we have mentioned.
This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages: