Excel Checkbox: If Checked then Change Cell Color (2 Methods)

In Microsoft Excel, using the checkbox or the tick box is not very common. But you will see this to solve a lot of problems. It basically creates a great visual for your Excel worksheets. Once you insert a checkbox in your Excel spreadsheet, you can perform many operations with this tool. In this tutorial, I will show you how to change the background color of a cell if the checkbox is checked in Excel.

This tutorial will demonstrate a step-by-step procedure with suitable examples and proper illustrations. So, stay with us.

How to Insert Checkboxes in Excel

1. Insert a Single Checkbox

Now, what is a checkbox? The checkbox is one kind of Excel form control that authorizes a user to tick or untick a box.

Before you start to do anything with the checkbox, you have to first know how to add a checkbox in Excel. By following the simple steps, you can easily insert a checkbox in your Excel Worksheet.

📌 Steps

• First, go to the Developer tab in the ribbon. If you don’t see any developer tab, then you have to enable the Developer tab.

• Now, from the Developer tab, click the Insert option. From the Form Controls, click on the Checkbox.

• After that, you will find a plus sign(+) that indicates the checkbox. Now, place it in any cell.
• After that, you will see the following:

In this way, you can insert a checkbox in your Excel worksheet.

2. Insert Multiple Checkboxes

Now, if we want to insert multiple checkboxes, do we have to insert them one by one again? This method seems pretty hectic. Thankfully, you can use the VBA code to insert multiple checkboxes in your worksheet.

Have a look at the screenshot:

Here, we will insert multiple checkboxes in the Checkbox column with VBA.

📌 Steps

• First, press Alt+F11 on your keyboard to open the VBA editor.
• Then, select Insert>Module.

• After that, type the following code:

Sub add_multiple_checkbox()

Dim cell As Range

Dim shape_of_box As CheckBox

  For Each cell In Selection.Cells

    Set shape_of_box = ActiveSheet.CheckBoxes.Add(cell.Left, cell.Top, cell.Width, cell.Height)

    With shape_of_box

      .Text = ""

      .Width = cell.Width

      .LinkedCell = cell.Offset(0, 1).Address

    End With

  Next cell

End

End Sub

• Then, save the file.
• Now, select the range of cells B5:B10.

• After that, press Alt+F8 on your keyboard to open the Macro dialog box.

• Next, select add_multiple_checkbox.
• Next, click on Run.

As you can see, there are multiple checkboxes in the Checkbox column. Now, you can tick the box and set the adjacent value to TRUE or FALSE.

If you want to change the cell background color if the checkbox is checked, follow the previous methods that were shown earlier.

Customize the Checkbox in Excel

Now, you can customize the checkbox according to your want.

You can drag the following to make it bigger or smaller:

You can change the “Check Box 1” name to another name. Just right-click on the checkbox.

Now, click on the Edit Text option. And after that, you can give it to any name.

Link the Checkbox with a Cell

Now, you have inserted a checkbox. You have to link it to a cell. It will show TRUE or FALSE in that cell. If you check the box, it will show TRUE. Otherwise, it will show FALSE.

📌 Steps

• First, right-click on the checkbox.

• Now, click on the Format Control option.

• Then, from the Format Control dialog box, click on the Control In the Cell link box, and type the cell you want to link with the checkbox. After that, click on OK.

• After that, you will see the FALSE or TRUE value in the cell that you linked with the checkbox. Here, it is showing FALSE because the checkbox is unchecked.
• If you tick the checkbox, you will see the following:

In this way, you can link a checkbox with a cell. By using this cell we will change the background color of a cell if the checkbox is checked.

How to Change Cell Color in Excel If Checkbox is Checked: 2 Methods

Now, there are two ways to change the format or change the background color of the cell if the checkbox is checked. I recommend you learn and apply these two methods to your Excel worksheet. It will surely increase your Excel knowledge. Let’s get into it.

1. Use Excel Conditional Formatting to Change Color If Checkbox Is Checked

To change the background color of a cell, you can use Conditional Formatting. It will help you to change the format of the particular cells in your Excel worksheet.

Have a look at the screenshot:

Here, we will work the same checkbox. Our goal is to change the background color of Cell D4 if the checkbox is checked. We will do that based on the TRUE or FALSE value in Cell C4. Basically, if you tick the box, Cell C4 will be TRUE. If Cell C4 is TRUE, then it will change the background color of Cell D4.

📌 Steps

• First, click on Cell D4.
• Then, from the Home tab, click on Conditional Formatting.

• Now, click on Manage Rules.

• Next, click on New Rule.

• Now, first, select the option “Use a formula to determine which cells to format”. After that, type the following formula in the box:

• Next, click on Format.

• From the Format Cells dialog box, click on Fill Then, choose any color and click on OK.

• Your formula and the background color are set. Now, click on OK.

• Again, click on OK.

• Here, you can see, our Cell C4 value is FALSE. For that reason, Cell D4 has no color format yet.
• Now, tick the checkbox.

As you see, it changes the cell color if the checkbox is checked in your Excel worksheet.

Read More: How to Apply Conditional Formatting Using Checkbox in Excel

2. Use Excel VBA to Change Color If Checkbox Is Checked

Now, if you are a VBA freak and love to solve problems with VBA codes, then this method is for you. You can change the cell background color if the checkbox is checked. Follow these simple steps.

📌 Steps

• First, right-click on the checkbox.

• Then, click on Assign Macro.

• Now, give the Macro a name. Click on New. It will open the Visual Basic Editor.
• After that, type the following code:

Sub change_cell_color()

Dim rng As Range

Set rng = Range("D4")

If Range("C4").Value = True Then

    rng.Interior.Color = vbRed

Else

    rng.Interior.Color = xlNone

End If

End Sub

• Now, save the file.
• If you check the box, it will change the cell color to Red.

In this way, you can use the VBA code to change the background color of a cell if the checkbox is checked.

Set a Cell Value in Excel If Checkbox Is Checked

Now, you can again use the VBA code to set a cell value if the checkbox is checked in your Excel worksheet.

Have a look at the screenshot:

Here, we inserted another checkbox. Now, right-click on the checkbox and click on Assign macro.

Now, type the following macro:

Sub set_cell_value()

Dim rng As Range

Dim c As Range, cell_value As String

Set rng = Range("D4")

If Range("C4").Value = True Then

    rng.Value = "Done!"

Else

    rng.Value = ""

End If

End Sub

Next, save the file. You can see no value yet.

Now, click on the checkbox. And you will see a value in Cell D4:

As you can see, we have set a cell value if the checkbox is checked in our Excel worksheet.

Read More: What Can You Do with Cell Value If Checkbox Is Checked in Excel?

💬 Things to Remember

✎ You can also insert a checkbox from the ActiveX control. But, we showed you the Form Control checkbox and its operations.

✎ You can also change the color of multiple cells if the checkbox is checked. Simply, select the range of cells you want to color and follow a similar process in Conditional Formatting.

✎ You Must save the file in .xlsm format.

Download Practice Workbook

Download this practice workbook.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to change the cell background color if the checkbox is checked in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Related Articles

• If Checkbox Is Checked Then Apply Formula in Excel
• VBA to Check If CheckBox Is Checked in Excel
• Excel VBA: Form Control Checkbox Value

<< Go Back to Excel CheckBox | Form Control in Excel | Learn Excel