A.N.M. Mohaimen Shanto
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.
- 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:
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 showed earlier.
Read More: How to Link Multiple Checkboxes in Excel
Similar Readings
- VBA to Check If CheckBox Is Checked in Excel
- How to Add Checkbox in Excel without Using Developer Tab
- If Checkbox Is Checked Then Apply Formula in Excel
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, 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.
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:
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:
=$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.
- 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 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.
- 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.
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.
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 SubNext, 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.
