3 Ways to Highlight a Cell or Row With a Checkbox in Excel

Want to highlight a cell or row with a checkbox in Excel to create more interactive and dynamic spreadsheets? Here are 3 easy ways to do it.

Microsoft Excel comes with a number of features that allow users to create basic to advanced-level spreadsheets, making professional lives easier. However, when you are dealing with a large set of complicated data in Excel, it's quite easy to feel lost.

One easy way of simplifying the spreadsheets is by highlighting checkboxes and rows with checkboxes so that they stand out. Below, we have discussed different ways of doing so in detail.

1. Highlight a Cell or Row Using a VBA Code

To highlight a cell or a row in Excel with a checkbox, the first method we will discuss involves using a VBA code. VBA is essentially a programming language in Excel that is used to automate tasks. You input a code that is relevant to your requirements, and Excel will perform the task for you without you having to spend hours doing it manually.

We all know how minutes in Excel can feel like hours when you have to do everything manually. The VBA code manipulates large amounts of data to make this process easier for you. Follow these steps to proceed with this method:

1. Launch Excel and open the targeted worksheet.
2. Right-click on the Sheet tab at the bottom and choose View Code in the context menu.
   
3. This should open the Microsoft Visual Basic for Applications dialog. Copy the code mentioned below and paste it into the VBA window. You can modify the cell ranges, height, and width according to your requirements.

   Sub AddCheckBox()Dim cell As Range Application.ScreenUpdating = False For Each cell In Range("A1:A12") With ActiveSheet.CheckBoxes.Add(cell.Left, _ cell.Top, cell.Width = 17, cell.Height = 15) .LinkedCell = cell.Offset(, 5).Address(External:=False).Interior.ColorIndex = xlNone .Caption = "" End With Next With Range("A1:A12") .Rows.RowHeight = 17 End With Range("A1").EntireColumn.Select Selection.ColumnWidth = 6# Range("B1").Select Application.ScreenUpdating = True End Sub

   

2. Highlight a Cell or Row Using Conditional Formatting

The next method on our list for highlighting a cell or row is by using the Conditional Formatting option. The Conditional Formatting feature allows users to modify the appearance of cells based on preferred criteria.

In this method, we will apply a condition that will label the marked checkboxes as True and highlight them (this can be modified per your requirements). The rest of the rows will be in their standard format, which will make it easier for you to differentiate between the tasks that are done and those that are incomplete.

Here is how you can use it to highlight a cell or a row:

1. Before you apply conditional formatting, you must add checkboxes to the table. For this, head over to the Developer tab in Excel.
2. In the Controls section, choose Insert and click on the checkbox icon in the Form Controls section.
   
3. Add the checkbox to the cell you want.
4. Then, select the cell with the checkbox and drag the cursor down to the end of the table. This will add a checkbox to all the cells in the table.
   
5. Now, right-click on the first cell with the checkbox and choose Format Control from the context menu. This should launch a Format Object dialog.
   
6. Locate the Cell link option and enter the cell name to which you want to link your first checkbox. As an example, our first checkbox is in cell H4, and we want to label the cell right in front of it, so we will add I4 in the text field.
   
7. Click OK to apply the changes and perform the same steps for the rest of the checkboxes in your table.
   
8. Once done, select the rows that you want to highlight and click on the Conditional Formatting option in the Home tab.
   
9. Select New Rule from the context menu.
   
10. In the following dialog, click on Use a formula to determine which cells to format under the Select a Rule Type option.
    
11. Now, head over to the Edit the Rule Description section and type =IF($I4=TRUE,TRUE,FALSE) in the text field.
    
12. Hit the Format button and choose a highlighting color.
13. Then, click OK to save the changes.
    

The selected rows or cells should be highlighted with conditional formatting.

3. Highlight Rows in Different Colors via Conditional Formatting

There can be times when you would want to highlight different rows in different colors to organize your spreadsheets better. Fortunately, Conditional Formatting allows you to do that as well.

For instance, if you are listing the prices of the items in your shop, and you want to highlight the ones more than 30 and more than 45, you can highlight such rows with different colors. Here is how:

1. Select the rows in the table that you want to highlight (typically the entire dataset).
2. In the Home tab and click on the Conditional Formatting option.
3. Select New Rules.
4. In the following dialog, select Use a formula to determine which cells to format in the Select a Rule Type section.
5. Type the following formula in the text field for Format values where this formula is true. Keep in mind, that we are applying the formula according to our table. To make it work for yourself, you must change the values accordingly.

   $C4>20

   
6. Click on the Format button and choose a color.
7. Click OK to save the changes.
   
8. Finally, hit OK again. The rows with a duration of more than 20 will now be highlighted.
   
9. Now, select the New Rule button again and choose Use a formula to determine which cells to format in the Select a Rule Type section.
10. Type the following formula in the text field for Format values where this formula is true.

    $C4>35

    
11. Click on the Format button and choose a color.
12. Select OK.
    
13. Click OK again to save the changes.
    

This should help you highlight rows and cells with different colors in Excel. If you want to cross something out in the sheet, you can strikethrough in Excel.

Bring Your Spreadsheets to Life With Color

Highlighting the rows and cells in your Excel spreadsheet can have numerous benefits. Apart from bringing your sheets to life, doing so will make the data easier on your eyes.