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

Start a conditional rule in Excel
Start a conditional rule in Excel

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.
New formatting rule and format cells dialog box
New formatting rule and format cells dialog box
  1. There, click the Format only cells that contain rule inside the Select a Rule Type container.
  2. 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.
  3. Then, click the Format button and select the Fill tab.
  4. Now, choose a green background color and hit the OK button.
  5. Again select OK on the New Formatting Rule dialog box.
  6. As soon as the Conditional Formatting dialog box closes, you should see that Excel highlighted all the “Yes” cells with the green color.
Applying Red conditional formatting for No
Applying Red conditional formatting for No
  1. 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.
Highlighted Yes with green and No with blue in Excel
Highlighted Yes with green and No with blue in Excel

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:

Using the SEARCH function
Using the SEARCH function
SEARCH function for NO
SEARCH function for NO

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:

Applying filter for Yesses
Applying filter for Yesses
  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.
Highlighting Yes with green in Excel
Highlighting Yes with green in Excel
  1. Select all the “Yes” values in the filtered column and highlight the cells by choosing a green shade from Home > Fill Color.
  2. Click the Filter dropdown again and this time keep No and uncheck all other items.
Highlighting No with red in Excel
Highlighting No with red in Excel
  1. Follow the above process to choose a red background color for cells containing nos.
Clear filter on Excel
Clear filter on Excel
  1. Select the dropdown arrow of the Yes/No value column header again and choose the Clear Filter option on the context menu.
  2. Also, hit the OK button on the context menu.
Color coded yes and no in excel using filter function
Color-coded yes and no in Excel using the filter function
  1. 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:

Save Excel sheet as Excel Macro-Enabled Workbook
Save Excel sheet as Excel Macro-Enabled Workbook
  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.
Making Yes to green and No to red in Excel on VBA Editor
Making Yes to green and No to red in Excel on VBA Editor
  1. Now, hit the Alt + F11 keys altogether to bring up the Excel VBA Editor tool.
  2. Within the VBA Editor, click Insert and then choose Module.
  3. 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
  1. Click the Save button on the VBA Editor toolbar and close the tool.
  2. Now, select the cell range that contains yess and nos for background color highlighting.
  3. Hit Alt + F8 to open the Macro dialog box.
Running a VBA macro on Excel
Running a VBA macro on Excel
  1. There, select the HighlightYesNo macro and hit the Run button.
  2. 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:

Using Excel Automate to make yes green and no red
Using Excel Automate to make yes green and no red
  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")
  1. Click Save script and then select the Run button.
  2. Don’t forget to choose the cell range where you want to apply the above Office Scripts functionality.


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.

Similar Posts