How to create tick boxes and highlight a row

Question

How to create tick boxes and highlight a row

Slick RCBD 96

About ten years ago I had a team lead that used to send out spreadsheets that had these tick boxes on them that when clicked it would highlight the entire row it was on. They were used for inventory checklists when we were verifying all the correct serial numbers had been received and there were no mix-ups in shipping as we'd tick each item off the list on a laptop the highlighting made it easier to spot what we were still looking for. Also later when installing the computers we'd use the tick boxes to show what still needed to be done.

Unfortunately I lost contact with that boss after I was laid off during COVID and I don't recall how he set it up. I can see how to use conditional formatting to highlight a specific cell, but how would I set up tick boxes to change the background to an entire row to be say light yellow instead of white? I also don't remember how to set up tick/check boxes at all, but that's a minor detail since just making it so the cell is empty or not would suffice for my purposes.

The embarrassing thing is that I'm fairly certain I could have done this a quarter century ago back in Y2K with Office 97 when I was in community college, but lack of use of the more advanced features means I've forgotten so much I learned back in school.

I'm using Office 2007 since I never bothered to pay for a newer version.

Could anybody link me to a tutorial or provide a simple one how to make a checkbox that will highlight/unhighlight a row? I tried searching online but it only shows a cell. It's acceptable if I have to settle for the cell value being 0 or 1 (or true/false).

0 comments

1 answer

Your answer

Answer 1

Hi Slick,

Good day. What you want are can be done in your Excel 2007. The easiest setup is to use a checkbox linked to a cell, then we will use Conditional Formatting to highlight the whole row.

First show the Developer tab. Click the Office Button > Excel Options > Popular. Check Show Developer tab in Ribbon, then click OK.
Insert a checkbox. Go to Developer > Insert > Check Box under Form Controls. Click and drag to place the checkbox in your first row. Right-click it > Edit Text, and remove the default "Check Box 1" text if you don't need it.
Link the checkbox to a cell. Right-click the checkbox > Format Control > Control. In Cell link, select an unused cell, such as A2. Click OK. When checked, that cell will show TRUE, when unchecked, it will show FALSE.
Highlight the entire row. Select the range you want highlighted, for example B2:H100. Go to Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format. Enter: =$A2=TRUE Click Format > Fill, choose light yellow, then click OK.

Now checking the box will turn the entire row yellow, and unchecking it will remove the highlighting.

You can also copy the checkbox to the other rows, but each checkbox needs to be linked to its corresponding cell like A3, A4, A5, etc. You can hide column A afterward if you don't want the TRUE/FALSE values to be visible.

I hope this helps.

Regards,
Jhun