How to Make Yes Green and No Red in Excel: [4 Ways]

Need to highlight Yes and No texts on cells easily visible to the audience? Learn here how to make “Yes” green and “No” red in Excel.

Green for “Yes” and red for “No” is a widely-used visual technique in user interfaces and other forms of communication, as it allows users to quickly distinguish between the two options. Green is often associated with a positive reaction or response, while red typically signifies a negative one.

In addition to aiding users in distinguishing between options, using green for “Yes” can draw their attention to it first, while red can prompt them to pause and consider before making a choice.

This blog post will provide guidance on how to use various tools, such as Excel’s Conditional Formatting, Filter options, VBA code, and Office Scripts code, to implement this color-coding technique in your Excel spreadsheets.

Reasons to Highlight Yes to Green and No to Red in Excel

• By using colors to highlight “Yes” and “No” responses, you make it easier to quickly identify the relevant information in a spreadsheet.
• The audience of the data can easily make informed decisions by looking at the color-coded “Yes” and “No” values instead of black and white text.
• Color coding can make a spreadsheet more visually appealing and engaging. Consequently, you can draw the attention of your audience to important information on an Excel sheet.
• You can easily convey the information effectively by color-coding “Yes” and “No” responses on your Excel report.
• Many employers may ask you to do this during an interview for a data analyst or data entry role.

Best Ways to Make Yes Green and No Red in Excel

Find below quick and proven ways to apply red and green color coding to “Yes” and “No” in Excel:

1. Using Conditional Formatting

One of the basic ways to modify the cell color of Yes and No values on your Excel worksheet is by applying a Conditional Formatting rule to the range of cells where the values exist. Here’s how you can do this on your end:

1. Select the cell range that contains all the “Yes” and “No” values in your spreadsheet.
2. Click the Conditional Formatting command inside the Styles block of the File tab.
3. On the context menu that pops up, click New Rule.
4. The New Formatting Rule dialog box will show up.

5. There, click the Format only cells that contain rule inside the Select a Rule Type container.
6. Now, go to the Edit the Rule Description container and make the following adjustments:
   • Click the Cell Value dropdown and choose Specific Text.
   • Type Yes in the field marked in the image.
7. Then, click the Format button and select the Fill tab.
8. Now, choose a green background color and hit the OK button.
9. Again select OK on the New Formatting Rule dialog box.
10. As soon as the Conditional Formatting dialog box closes, you should see that Excel highlighted all the “Yes” cells with the green color.

11. Now, repeat the same steps above and modify the following to get red color for cells containing the No value:
    • In the Specific Text step, type No in the marked field.
    • Inside the Format > Fill tab, choose a red color.

That’s all! You should get similar formatting with green and red background color for “Yes” and “No” values.

Now, if you want to use various Excel formulas inside the Conditional Formatting feature to highlight No with red and Yes with green, you’re also welcome. Find below some formulas that let you do this and which Conditional Formatting rule to select:

=SEARCH("Yes",C2:C7)>0

=SEARCH("No",C2:C7)>0

Wondering where to apply these custom formulas? Just open the Conditional Formatting dialog box and choose Use a formula to determine which cells to format inside the Edit Formatting Rule container.

2. Using Filter

If you’ve just started using Excel to visualize and analyze data and still learning, then you can find the above method a bit challenging. Instead, use the Filter tool of Excel to filter out all the yeses on an Excel worksheet and manually highlight the cells with a green background color.

Once done, follow the same process for nos on your Excel spreadsheet but this time choose red instead of green for cell highlighting. Here’s how it works in a real-world scenario:

1. Highlight the column header under which the yesses and nos appear.
2. Click the Filter command inside the Sort & Filter block on the Data tab.
3. This will add a dropdown arrow for all the column headers.
4. Now, click the dropdown arrow for the “Yes” and “No” value column and uncheck every item except Yes.
5. Hit the OK button to filter the column with only Yes values.

6. Select all the “Yes” values in the filtered column and highlight the cells by choosing a green shade from Home > Fill Color.
7. Click the Filter dropdown again and this time keep No and uncheck all other items.

8. Follow the above process to choose a red background color for cells containing nos.

9. Select the dropdown arrow of the Yes/No value column header again and choose the Clear Filter option on the context menu.
10. Also, hit the OK button on the context menu.

11. You’ll see that Excel shows the entire dataset along with red and green highlighting for No and Yes, respectively.

3. Applying a VBA Script

One of the best and most highly used methods to highlight Yes and No with green and red colors in Excel is using a custom VBA macro. By creating a selection-based VBA script for this problem, you can simply select a cell range and add a green background for yesses and red background for nos.

This method is suitable for you if you want all or any of the following when making “Yes” green and “No” red in Excel:

• With VBA, you can automate the process of highlighting “Yes” to green and “No” to red, which can save you time and effort.
• VBA offers a wide range of formatting options, giving you greater flexibility and control over how you want to highlight the data.
• Once you have written the VBA code, you can reuse it in other Excel spreadsheets or modify it for other similar tasks, which can save you time in the long run.

Now that you know the utility of a VBA macro for the problem at hand, let’s see below how to exactly write and execute such a script in Excel:

1. Firstly, you must save the worksheet in XLSM format so that you can use and manage macro on it.
2. Hit the File tab and click Save As. There, also choose Browse to save the Excel file on a local or networked directory.
3. On the Save As dialog box, make sure you choose Excel Macro-Enabled Workbook within the Save as type dropdown box.
4. Hit the Save button to finalize the process.

5. Now, hit the Alt + F11 keys altogether to bring up the Excel VBA Editor tool.
6. Within the VBA Editor, click Insert and then choose Module.
7. Copy and paste the following VBA script into the new blank Module:

Sub HighlightYesNo()

Dim cell As Range

For Each cell In Selection
    If cell.Value = "Yes" Then
        cell.Interior.Color = RGB(0, 255, 0) 'Green
    ElseIf cell.Value = "No" Then
        cell.Interior.Color = RGB(255, 0, 0) 'Red
    End If
Next cell

End Sub

8. Click the Save button on the VBA Editor toolbar and close the tool.
9. Now, select the cell range that contains yess and nos for background color highlighting.
10. Hit Alt + F8 to open the Macro dialog box.

11. There, select the HighlightYesNo macro and hit the Run button.
12. Excel will instantly color code “Yes” to green and “No” to red cell background color.

4. Using Excel Automate/ Office Scripts

If you’re using Microsoft 365 subscription on the Excel for Windows desktop app or Excel on the web, you can try out Office Scripts to automate the Yes/No Green/Red color coding task. Moreover, you can make this script a part of a larger Excel Automate code for advanced automation. Let’s find below the code you must use and how:

1. Go to your Excel worksheet and click the Automate tab on the Excel ribbon.
2. Hit the New Script button located inside the Scripting Tools command block.
3. Now, copy and paste the following TypeScript code inside the Code Editor:

function main(workbook: ExcelScript.Workbook) {

    // get selected range
    let rng = workbook.getSelectedRange();
    let rows = rng.getRowCount();

    for (let i = 0; i < rows; i++) {

        // Set fill color to Green for the cell value
        if (rng.getCell(i, 0).getValue() == "Yes") {
            rng.getCell(i, 0).getFormat().getFill().setColor("92D050")
        }

        // Set fill color to Red for the cell value No
        if (rng.getCell(i, 0).getValue() == "No") {
            rng.getCell(i, 0).getFormat().getFill().setColor("FF0000")
        }
    }
}

4. Click Save script and then select the Run button.
5. Don’t forget to choose the cell range where you want to apply the above Office Scripts functionality.

Conclusions

So, now you know four different methods to make “Yes” green and “No” red in any Excel worksheet. Use the Filter tool if you’re just starting to learn the tool. Alternatively, utilize Conditional Formatting for more flexibility and functionalities.

Finally, you can use either a VBA script to Excel Automate code to put the entire task into an elaborate Excel file automation project. Don’t forget to leave a comment below on your experience using any of the methods.