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

Get FREE Advanced Excel Exercises with Solutions!

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 insert a checkbox. 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.

How to Insert a Checkbox in Excel

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

Read More: Excel VBA: Form Control Checkbox Value


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:

Insert Multiple Checkboxes in Excel

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.

Insert Multiple Checkboxes in Excel

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

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

Insert Multiple Checkboxes in Excel

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.

Insert Multiple Checkboxes in Excel

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

Read More: How to Link Multiple Checkboxes in Excel


Similar Readings


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:

Customize the Checkbox in Excel

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.

Customize the Checkbox in Excel


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.

Link the Checkbox with a Cell

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

Link the Checkbox with a Cell

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

Link the Checkbox with a Cell

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.

Read More: How to Align Checkboxes in Excel


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:

Excel to Change Cell Color If Checkbox Is Checked 

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.

Excel to Change Cell Color If Checkbox Is Checked 

  • Now, click on Manage Rules.

  • Next, click on New Rule.

Excel to Change Cell Color If Checkbox Is Checked 

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

=$C$4=TRUE

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

Excel to Change Cell Color If Checkbox Is Checked 

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

Excel to Change Cell Color If Checkbox Is Checked 

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

Read More: How to Resize 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.

Excel to Change Cell Color If Checkbox Is Checked 

  • Then, click on Assign Macro.

  • Now, give the Macro a name. And 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.

Excel to Change Cell Color If Checkbox Is Checked 

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

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


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.

Set a Cell Value in Excel If Checkbox Is Checked 

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.

Set a Cell Value in Excel If Checkbox Is Checked 

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

Set a Cell Value in Excel If Checkbox Is Checked 

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

A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo