How to Make Yes Green and No Red in Excel – 8 Examples

Example 1 – Highlighting Cells Rules

The dataset showcases students’ names and math scores.

Highlighting Cells Rules to Make Yes Green and No Red in Excel

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.

use IF function

  • 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.

select TEXT that Contains option

  • 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.

Finally able to Make Yes Green and No Red in Excel

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


Method 2 – Using the SEARCH Function

Using SEARCH Function to Make Yes Green and No Red in Excel

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.

apply IF function

  • 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.

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.

apply the formula

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

select desired color

  • Click OK.

format the color

This is the output.

get 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.

select red color

  • Click OK.

This is the output.

Finally able to Make Yes Green and No Red in Excel

Read More: Conditional Formatting with Formula in Excel


Method 3 – Applying the SEARCH and ISNUMBER Functions

 

Applying SEARCH and ISNUMBER Functions to Make Yes Green and No Red in Excel

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.

select conditional formatting to Make Yes Green and No Red in Excel

  • 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.

format the color

This is the output.

Finally able to Make Yes Green and No Red

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


Method 4 – Using the FIND Function

Using FIND Function to Make Yes Green and No Red in Excel

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.

using IF function

  • 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.

select conditional formatting

  • 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.

format the color to Make Yes Green and No Red in Excel

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.

format red color to Make Yes Green and No Red in Excel

This is the output.

Finally able to Make Yes Green and No Red in Excel

Read More: Applying Conditional Formatting for Multiple Conditions in Excel


Method 5. Utilizing the COUNTIF Function

Utilizing COUNTIF Function to Make Yes Green and No Red in Excel

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.

Using IF function

  • 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.

select conditional formatting

  • 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.

format the color

This is the output.

Finally able to Make Yes Green and No Red in Excel


Method 6 – Combining the COUNT and SEARCH Functions

Combining COUNT and SEARCH Functions to Make Yes Green and No Red in Excel

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.

select conditional formatting

  • 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.

format the color

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.

format No as red color

This is the output.

Finally able to Make Yes Green and No Red in Excel


Method 7 – Applying the IF and the SEARCH Functions

Applying IF and SEARCH Functions to Make Yes Green and No Red in Excel

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.

apply IF function

  • 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.

select conditional formatting

  • 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.

format the YES as Green color

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.

Finally able to Make Yes Green and No Red in Excel

Read More: Excel Conditional Formatting Formula with IF


Method 8 – Utilizing the MATCH Function

Utilizing MATCH Function to Make Yes Green and No Red in ExcelSteps:

  • 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.

apply IF function

  • 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.

select conditional formatting

  • 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.

format Yes as green color

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.

Make No Red in Excel

This is the output.

Finally able to Make Yes Green and No Red in Excel

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


Download Practice Workbook

Download this practice workbook to exercise.


Related Articles

<< Go Back to Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo