How To Check The Checkbox To Change The Cell Color

Date:2020-12-21 Author:Sandra

This is a very cool effect in Excel. When you checked each checkbox, the color of the related row would be changed at the same time.

How To Check The Checkbox To Change The Cell Color

Ok, let’s get started. The content of this tutorial may be a lot, but it is not complicated. The author suggests you open an Excel sheet to have a try.

Step 1: Create the Basic Part of Table

Prepare the basic part of the “Supermarket Shopping List”. Of course, you can create others. This step would be easy if you always use Excel in your daily work. You can choose the colors you like as Fill color, but light colors would be better.

How To Check The Checkbox To Change The Cell Color

Step 2: Insert Controls

Go to Developer and click on Insert. Choose check boxes in the list.

How To Check The Checkbox To Change The Cell Color

You can delete the “Check Box 1” text to looks more beautiful.

How To Check The Checkbox To Change The Cell Color

We consider column D as an auxiliary column, and enter TRUE in this column except title row.

How To Check The Checkbox To Change The Cell Color

Step 4:  Create Cell Link

Check the first checkbox and right-click on the checkbox, choose Format Control.

How To Check The Checkbox To Change The Cell Color

Make this checkbox in A2 link cell D2. Repeat this step for every checkboxes.( Checkbox in A3 cell link D3……)

How To Check The Checkbox To Change The Cell Color

After this step, you will find that when the checkbox is checked, the related D value will display TRUE, If it is not checked, the value FALSE will be displayed.

How To Check The Checkbox To Change The Cell Color

Step 5: Create New Formatting Rule

Highlight A2 to C2 first and go to Home and click on Conditional Formatting in the toolbar, choose New Formatting Rule in the list. And then Choose the last option “Use a Formula to determine which cells to format” in the New Formatting Rule window.

How To Check The Checkbox To Change The Cell Color
How To Check The Checkbox To Change The Cell Color

Now, you need to edit the rule description like the GIF below. Do these settings for each row.

How To Check The Checkbox To Change The Cell Color
How To Check The Checkbox To Change The Cell Color

Step 6: Hide the Auxiliary Column

Select the column D and right-click it, choose Hide in the list. It’s just for a better look.

How To Check The Checkbox To Change The Cell Color

Now, you have finished all the steps. That’s really cool.

How To Check The Checkbox To Change The Cell Color

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

Leave a Reply

Your email address will not be published. Required fields are marked *