Do you need to make Yes green and No red in your Excel Report or dashboard?

Making Yes green and No red is a popular visual technique, often used in user interfaces and other forms of communication to help users quickly identify the differences between the two options.

Yes in green often signifies that the user is choosing a positive reaction or response, whereas No in red typically denotes a negative reaction or response.

In addition to helping users quickly differentiate between two options. Making Yes green can draw your user’s attention to it first, while red can make the user pause and think before making a choice.

In this blog post, you will use excel Conditional Formatting, Filter options, VBA code, and Office Scripts code to make yes green and no red in Excel.

Get your copy of the example workbook used in this post to follow along!

Make Yes Green and No Red with Conditional Format

Conditional formatting in Excel is a powerful feature that allows you to quickly and easily highlight data that meets specific criteria. This feature is especially useful for sorting, analyzing data, and visualizations such as heat maps and data bars in a spreadsheet. With conditional formatting, you can set rules to apply specific formatting to cells that meet certain criteria.

Green is a great color to use for Yes and Red is for No.

Suppose you have a table of data in excel with a Yes/No column and need to fill the cell background color green for yes and red for no. 

Here are the steps to use conditional formatting to make Yes green and No red.

1. Select the range of cells containing your yes and no values. In this example, the range is D3:D7.
2. Go to the Home tab.
3. Choose Conditional Formatting options.
4. Click New Rule to open the New Formatting Rule dialog box.

5. Select the the Format only cells that contain option in the Select a Rule Type list.
6. Choose the Specific Text option from the first dropdown list in Edit the Rule Description container.
7. Select containing in the second dropdown.
8. Type the text Yes in the input box. Alternatively, You can refer to any cell containing a Yes value.
9. Click the Format button to open the Format cells dialog box and assign the formats to the cells containing the text Yes.

10. Choose Fill Tab in the Format Cells dialog box
11. Select the Green color from the color palette
12. Press the OK button.
13. Press OK on the New Formatting Rule dialog box.

The above steps fill a green color to any cells containing a Yes value in your selected range.

But you will also need to create another conditional format rule to make any No values appear in red.

Repeat the same steps as above but this time in step 8 type the text No in the input box and in step 11 choose a red color.

Your final output should look like the above example.

When you change any of the yes or no values in your selected range the colors will update to reflect the new values.

Make Yes Green and No Red with Filter

Filters in excel are a handy tool for analyzing data.

In particular, you can use them to easily select all the yes values and color them green.

Please follow the below steps to make Yes green and No red with the Filter toggles.

1. Select your range of Yes and No values.
2. Go to the Home tab.
3. Choose the Filter option in the Sort & Filter command list.

This will add the sort and filter toggles to your data set and you should see a small down arrow icon present on the right side of each column heading.

4. Click the down arrow in the yes and no column to expand the filter options.
5. Make sure you have selected only Yes in the list of values from the column.
6. Press the OK button.

Now you have a filtered list that only contains the Yes records in the table.

7. Select the cells that exclude the header cell.
8. Click on the Fill Color command in the Home tab.
9. Select a Green color to fill the cell containing Yes.

You can repeat the same steps to make No red.

Now each yes and no value has been colored, but this won’t be a dynamic solution. You will need to repeat the process anytime you need to change the yes or no values.

Make Yes Green and No Red with VBA

VBA is a programming language in Excel that enables you to automate tasks.

You can create macros to manipulate data and perform calculations in the spreadsheet. It also allows you to define if-then statements in your code for logical automation.

Press the Alt + F11 keyboard shortcut to open the visual basic editor where you can add your VBA code.

Go to the Insert menu in the visual basic editor and select the Module option. This is where you can write your own code.

Sub YesGreenNoRed()
' Loop through the selected range of cells
For Each cel In Selection
    
    ' Make Yes Green
    If cel.Value = "Yes" Then cel.Interior.Color = 5296274
    
    ' Make No Red
    If cel.Value = "No" Then cel.Interior.Color = 255
Next
End Sub

Add the above code to your module in the visual basic editor.

To Run the YesGreenNoRed Macro

1. Select the range of cells that you want to color.
2. Press Alt + F8 to open the Macro menu.
3. Select the YesGreenNoRed macro.
4. Press the Run button.

The code loop through the selected range and the script checks the value in each cell and fills either a green or red color depending on if the value is yes or no.

Make Yes Green and No Red with Office Scripts

Office Scripts in excel for the web allow users to create scripts that can automate processes such as repetitive tasks, creating charts, formatting cells, and more.

You can find the Office Scripts options in the Automate tab of the ribbon. Click on the New Script command to open the script editor where you can add your code.

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")
    }
  }
}

Add the above code to the Code Editor and press the Save button.

You can then run this script to change the fill color for your yes and no values. Select the range of cells with your yes and no values and press the Run button.

The code loop through the selected range and the script checks the cell value and fills green for Yes and red for No.

Conclusions

This post has shown you several ways to make your yes values green and no values red.

The Conditional Formatting method will dynamically Make Yes Green and No Red based on the current cell text. You can change the cell value to Yes or No and see the color change.

The Filter option is a handy method that makes manually updating the colors a much quicker option.

Both VBA and Office Scripts approach helps you to automatically change the colors based on the values in your selected range.

Do you have any other tips for making Yes green and No red? Let me know in the comments below!