### Example 1 – Highlighting Cells Rules

We have a dataset showing students’ names and math scores. **the IF Function** will be used.

**Steps:**

- Enter the following formula in
**D5**to display pass and fail in the**Passed**column.

`=IF(C5>40,"Yes","No")`

The formula will check whether the value of **C5** is greater than 40. If the condition is met, the function will return Yes. Otherwise, it returns No.

- Press
**Enter**.

- Drag the
**Fill Handle**to fill the other cells. - This is the output.

- Select the cell range and go to the
**Home**tab. - Select
**Conditional Formatting**. - Choose
**Highlight Cells Rules**. - Select
**Text that Contains**.

- In the
**Text That Contains**dialog box, enterÂ**Yes.** - Choose a formatting style (here,
**Green File with Dark Green Text**). - Click
**OK**.

- Repeat the above process
- In the
**Text That Contains**dialog box, enter No and select a formatting style (here,**Light Red Fill with Dark Red Text**).

- Click
**OK**.

- This is the output.

**Read More:** Excel Highlight Cell If Value Greater Than Another Cell

### Method 2 – Using the SEARCH Function

**the SEARCH function** will be used.

**Steps:**

- Use the following formula in
**D5**to display pass and fail in the**Passed**column.

`=IF(C5>40,"Yes","No")`

The formula checks whether the value of **C5** is greater than 40. If the condition is met, the function will return Yes. Otherwise, it returns No.

- Press
**Enter**.

- Drag the
**Fill Handle**to fill the other cells. - This is the output.

- Select the range and go to the
**Home**tab. - Choose
**Conditional Formatting**. - Select
**New Rule**.

- In the
**New Formatting Rule**window, select**Use a formula to determine which cells to format**. - Enter the following formula in
**Format values where this formula is true**.

`=SEARCH("Yes",$D5)>0`

The **SEARCH** function will look for Yes in the cells of column **D** and return a value for Yes.

- Click
**Format**.

- In the
**Format Cells**dialog box, In**Fill**, choose**Green**as**Background Color.** - Click
**OK**.

- Click
**OK**.

- This is the output.

- Follow the same procedure to see no in red.
- In the
**New Formatting Rule**window, select**Use a formula to determine which cells to format**. - Enter the following formula in the
**Format values where this formula is true**.

`=SEARCH("No",$D5)>0`

The** SEARCH** function will look for No in the cells of column **D** return a value.

- Click
**Format**.

- In the
**Format Cells**dialog box, select**Fill.** - Choose
**Red**as**Background Color**, and click**OK**.

- Click
**OK**.

- This is the output.

**Read More:** Conditional Formatting with Formula in Excel

### Method 3 – Applying the SEARCH and ISNUMBER Functions

**Steps:**

- Use the following formula in
**D5.**.

`=IF(C5>40,"Yes","No")`

This formula will check whether the value ofÂ **C5** is greater than 40. If the condition is met, the function will return Yes. Otherwise, it returns No.

- Press
**Enter**.

- Drag the
**Fill Handle**to fill the other cells. - You will see the result in the
**Passed**column.

- Select the range.
- Go to the
**Home**tab and select**Conditional Formatting**. - Select
**New Rule**.

- In the
**New Formatting Rule**window, choose**Use a formula to determine which cells to format**. - Enter the following formula in
**Â Format values where this formula is true**.

`=ISNUMBER(SEARCH("Yes",$D5))`

The **SEARCH** function will look for Yes in the cells of column **D** and return a value. The** ISNUMBER** will returnÂ TRUE if it gets a numeric value. Otherwise, FALSE.

- Choose
**Green**as**Background ColorÂ**in**Format**. - Click
**OK**.

- This is the output.

- Follow the same procedure display no in red.
- In
**New Formatting Rule**, select**Use a formula to determine which cells to format**. - Enter the following formula in
**Format values where this formula is true**.

`=ISNUMBER(SEARCH("No",$D5))`

The** SEARCH** function will look for No in the cells of column **D** and return a value. The **ISNUMBER** will return TRUE if it gets a numeric value. Otherwise, FALSE.

- Choose
**Red**as**Background Color**in**Format**. - Click
**OK**.

- This is the output.

**Read More:** How to Apply Conditional Formatting to Each Row Individually

### Method 4 – Using the FIND Function

**the FIND function** will be used.

**Steps:**

- Use the following formula in
**D5**.

`=IF(C5>40,"Yes","No")`

This formula will check whether the value of **C5** is greater than 40. If the condition is met, the function will return Yes. Otherwise, it returns No.

- Press
**Enter**.

- Drag the
**Fill Handle**to fill the other cells. - This is the output.

- Select the range and go to the
**Home**tab. - Choose
**Conditional Formatting**. - Select
**New Rule**.

- In the
**New Formatting Rule**window, select**Use a formula to determine which cells to format**. - Enter the following formula in
**Format values where this formula is true**.

`=Find("Yes",$D5)`

The** FIND** function will look for Yes in the cells of column **D** and, finding matches, will return Yes. No matches will not return any value.

- Choose
**Green**as**Background Color**in**Format**. - Click
**OK**.

- This is the output.

- Follow the same procedure to display no in red
- In
**New Formatting Rule**, select**Use a formula to determine which cells to format**. - Enter the following formula in
**Format values where this formula is true**.

`=Find("No",$D5)`

The **FIND** function will look for Yes in the cells of column** D** and, finding matches, will return Yes. Yes. No matches will not return any value.

- Choose
**Red**as**Background Color**in**Format**. - Click
**OK**.

- This is the output.

**Read More:** Applying Conditional Formatting for Multiple Conditions in Excel

### Method 5. Utilizing the COUNTIF Function

**the COUNTIF function** will be used.

**Steps:**

- Use the following formula inÂ
**D5**to display pass and fail in the Passed column.

`=IF(C5>40,"Yes","No")`

The formula will check whether the value of **C5** is greater than 40. If the condition is met, the function will return Yes. Otherwise, it returns No.

- Press
**Enter**.

- Drag the
**Fill Handle**to fill the other cells. - This is the output.

- Select the range and go to the
**Home**tab. - Choose
**Conditional Formatting**. - Select
**New Rule**.

- In the
**New Formatting Rule**window, select**Use a formula to determine which cells to format**. - Enter the following formula in
**Format values where this formula is true**.

`=COUNTIF($D5,"*Yes*")`

The wildcard symbol (*) before and after Yes returns partial matches and the** COUNTIF** function will return the number of times this text appears in column** D**.

- Choose
**Green**as**Background Color**inÂ**Format**. - Click
**OK**.

- This is the output.

- Follow the same procedure to display no in red
- In
**New Formatting Rule**, select**Use a formula to determine which cells to format**. - Enter the following formula in
**Format values where this formula is true**.

`=COUNTIF($D5,"*No*")`

The wildcard symbol (*) before and after No returns partial matches and the **COUNTIF** function will return the number of times this text appears in column **D**.

- Choose
**Red**as**Background Color**in**Format**. - Click
**OK**.

- This is the output.

### Method 6 – Combining the COUNT and SEARCH Functions

**Steps:**

- Use the following formula in
**D5**to display pass and fail in the**Passed**column.

`=IF(C5>40,"Yes","No")`

This formula will check whether the value of **C5** is greater than 40. If the condition is met, the function will return Yes. Otherwise, it returns No.

- Press
**Enter**.

- Drag the
**Fill Handle**to fill the other cells. - This is the output.

- Select the range and go to the
**Home**tab. - Choose
**Conditional Formatting**. - Select
**New Rule**.

- In
**New Formatting Rule**, select**Use a formula to determine which cells to format**. - Enter the following formula in
**Format values where this formula is true**.

`=COUNT(SEARCH("Yes",$D5))`

The **SEARCH** function will look for Yes in column **D** and,Â finding matches, it will return a value. The** COUNT** function will return 1 if it gets any number from the output of the **SEARCH** function, otherwise 0.

- Choose
**Green**as**Background Color**In**Format**. - Click
**OK**.

- This is the output.

- Follow the same procedure to display no in red
- In
**New Formatting Rule**, select**Use a formula to determine which cells to format**. - Enter the following formula in
**Format values where this formula is true**.

`=COUNT(SEARCH("No",$D5))`

The **SEARCH** function will look for No in column **D** and, finding matches, will return a value. The **COUNT** function will return 1 if it gets any number from the output of the **SEARCH** function, otherwise 0.

- Choose
**Red**as**Background Color**inÂ**Format**. - Click
**OK**.

- This is the output.

### Method 7 – Applying the IF and the SEARCH Functions

**Steps:**

- Use the following formula in
**D5**to display pass and fail in the**Passed**column.

`=IF(C5>40,"Yes","No")`

The formula will check whether the value of **C5** is greater than 40. If the condition is met, the function will return Yes. Otherwise, it returns No.

- Press
**Enter**.

- Drag the
**Fill Handle**to fill the other cells. - This is the output.

- Select the range and go to the
**Home**tab. - Choose
**Conditional Formatting**. - Select
**New Rule**.

- In
**New Formatting Rule**, select**Use a formula to determine which cells to format**. - Enter the following formula in
**Format values where this formula is true**.

`=IF(SEARCH("Yes",$D5),1,0)>0`

The **SEARCH** function will look for Yes in column **D** and, finding matches, will return a value. The IF will return 1 if the **SEARCH** function finds any matches, otherwise 0, and for values greater than 0, it will return TRUE. Otherwise, FALSE.

- Next, choose
**Green**as**Background Color**in**Format**. - Click
**OK**.

- This is the output.

- Follow the same procedure to display no in red
- In
**New Formatting Rule**, select**Use a formula to determine which cells to format**. - Enter the following formula in
**Format values where this formula is true**.

`=IF(SEARCH("No",$D5),1,0)>0`

The **SEARCH** function will look for No in Column **D** and, finding matches, will return a value. The **IF** function will return 1 if the **SEARCH** function finds any matches, otherwise, 0.For values greater than 0, it will return TRUE, otherwise, FALSE.

- Choose
**Red**as**Background Color**in**Format**. - Click
**OK**.

- This is the output.

**Read More:** Excel Conditional Formatting Formula with IF

### Method 8 – Utilizing the MATCH Function

**the MATCH function** will be used.

**Steps:**

- Use the following formula in
**D5**to display pass and fail in the**Passed**column.

`=IF(C5>40,"Yes","No")`

This formula will check whether the value of **C5** is greater than 40. If the condition is met, the function will return Yes. Otherwise, it returns No.

- Press
**Enter**.

- Drag the
**Fill Handle**to fill the other cells. - This is the output.

- Select the range and go to the
**Home**tab. - Choose
**Conditional Formatting**. - Select
**New Rule**.

- In
**New Formatting Rule**, select**Use a formula to determine which cells to format**. - Enter the following formula in
**Format values where this formula is true**.

`=MATCH("*Yes*",$D5,0)`

The wildcard symbol (*) before and after Yes, returns complete matches and the **MATCH** function will return 1,Â finding any partial matches in column **D**.

- Next, choose
**Green**as**Background Color**in**Format**. - Click
**OK**.

- This is the output.

- Follow the same procedure to display no in red
- In
**New Formatting Rule**, select**Use a formula to determine which cells to format**. - Enter the following formula in
**Format values where this formula is true**.

`=MATCH("*No*",$D5,0)`

The wildcard symbol (*), before and after No, returns complete matches and the **MATCH** function will return 1, finding partial matches in column **D**.

- Choose
**Red**as**Background Color**in**Format**. - Click
**OK**.

- This is the output.

**Read More:** How to Apply Conditional Formatting with INDEX-MATCH in Excel

**Download Practice Workbook**

Download this practice workbook to exercise.

**Related Articles**

- How to Create a Rating Scale in Excel
- How to Use Conditional Formatting on Text Box in Excel
- How to Apply Borders in Excel with Conditional Formatting
- How to Apply Alignment in Excel Conditional Formatting
- How to Copy Conditional Formatting to Another Cell in Excel
- How to Copy Conditional Formatting Color to Another Cell in Excel
- How to Copy Conditional Formatting to Another Sheet
- How to Copy Conditional Formatting with Relative Cell References in Excel
- How to Copy Conditional Formatting But Change Reference Cell in Excel

**<< Go Back to Conditional Formatting | Learn Excel**