Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to highlight cell or row with checkbox in Excel?

As below screenshot shown, you need to highlight row or cell with checkbox. When a checkbox is checked, a specified row or a cell will be highlighted automatically. But how to achieve it in Excel? This article will show you two methods to achieve it.


Highlight cell or row with checkbox with Conditional Formatting


You can create a Conditional Formatting rule to highlight cell or row with checkbox in Excel. Please do as follows.

Link all check box to a specified cell

As below screenshot shown, all checkboxes are listed in column I, you need to link these checkboxes to their adjacent cells in column J.

1. Select the first check box in I1, enter formula =$J$1 into the formula bar, and then press the Enter key.

2. Repeat step 1 until all check boxes are linked to the adjacent cells.

Create a Conditional Formatting rule

Now you need to create a Conditional Formatting rule as follows step by step.

1. Select the rows you need to highlight with checkboxes, then click Conditional Formatting > New Rule under the Home tab. See screenshot:

2. In the New Formatting Rule dialog box, you need to:

2.1 Select the Use a formula to determine which cells to format option in the Select a Rule Type box;

2.2 Enter formula =IF($J1=TRUE,TRUE,FALSE) into the Format values where this formula is true box;

2.3 Click the Format button to specify a highlighted color for the rows;

2.4 Click the OK button. See screenshot:

Note: In the formula, $J1 is the first linked cell of the check box.

Now the Conditional Formatting rule is created. When checking the check boxes, the corresponding rows will be highlighted automatically as bellows screenshot shown.


Highlight cell or row with checkbox with VBA code

The following VBA code can also help you to highlight cell or row with checkbox in Excel. Please do as follows.

1. In the worksheet you need to highlight cell or row with checkbox. Right click the Sheet Tab and select View Code from the right-clicking menu to open the Microsoft Visual Basic for Applications window.

2. Then copy and paste the below VBA code into the Code window.

VBA code: Highlight row with checkbox in Excel

Sub AddCheckBox()
Dim xCell As Range
Dim xRng As Range
Dim I As Integer
Dim xChk As CheckBox
On Error Resume Next
InputC:
    Set xRng = Application.InputBox("Please select the column range to insert checkboxes:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
If xRng.Columns.Count > 1 Then
    MsgBox "The selected range should be a single column", vbInformation, "Kutools fro Excel"
    GoTo InputC
Else
    If xRng.Columns.Count = 1 Then
        For Each xCell In xRng        
            With ActiveSheet.CheckBoxes.Add(xCell.Left, _
               xCell.Top, xCell.Width = 15, xCell.Height = 12)
               .LinkedCell = xCell.Offset(, 1).Address(External:=False)
               .Interior.ColorIndex = xlNone
               .Caption = ""
               .Name = "Check Box " & xCell.Row
            End With    
            xRng.Rows(xCell.Row).Interior.ColorIndex = xlNone                  
        Next        
    End If    
    With xRng    
     .Rows.RowHeight = 16    
    End With   
    xRng.ColumnWidth = 5#    
    xRng.Cells(1, 1).Offset(0, 1).Select    
    For Each xChk In ActiveSheet.CheckBoxes   
      xChk.OnAction = ActiveSheet.Name + ".InsertBgColor"      
    Next
End If
End Sub

Sub InsertBgColor()
Dim xName As Integer
Dim xChk As CheckBox
For Each xChk In ActiveSheet.CheckBoxes 
  xName = Right(xChk.Name, Len(xChk.Name) - 10) 
  If (xName = Range(xChk.LinkedCell).Row) Then   
   If (Range(xChk.LinkedCell) = "True") Then   
    Range("A" & xName, Range(xChk.LinkedCell).Offset(0, -2)).Interior.ColorIndex = 6    
   Else    
    Range("A" & xName, Range(xChk.LinkedCell).Offset(0, -2)).Interior.ColorIndex = xlNone  
   End If  
  End If
Next
End Sub

3. Press the F5 key to run the code. (Note: you should put the cursor into the first part of the code to apply the F5 key) In the popping up Kutools for Excel dialog box, please select the range you want to insert check boxes, and then click the OK button. Here I select range I1:I6. See screenshot:

4. Then check boxes are inserted into selected cells. Check any one of the checkboxes, the corresponding row will be highlighted automatically as below screenshot shown.

Tip: If you want to insert multiple check boxes into a selected range in bulk, you can try the Batch Insert Check Boxes utility pf Kutools for Excel. Or batch insert multiple Option Buttons with the Batch Insert Option Buttons utility. Besides, you can delete all check boxes at once with the Batch Delete Check Boxes utility as below screenshots shown. You can go to free download the software with no limitation in 60 days.


Related articles:


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    PixelBoi · 1 months ago
    This is a horrendous article. It lacks a lot of information and steps. If you follow this article verbatim it will not end in the result you are seeking.

    Essentially the article is saying to have 2 columns where the checkboxes are linked to adjacent columns to enter in values which are then used for conditional formats. No one wants to have values associated in adjacent cells to checkboxes. Lastly, if this is the route you're going you're not linking checkboxes to conditional formats, you are linking checkboxes to cell values which are then in turn associated to conditional formats.

    It is easier to just use icons instead of checkboxes (use the green checkmark icon) and create the conditional formats for a value of 1 or 0. If cell = 1 then it will replace the value with the icon and highlight your row. To accomplish this you use 2 conditional formats on your table.

    Top left of table is B4, bottom right of table is L28

    1st conditional format:
    USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT
    Formula: =$B4=1
    Format: fill
    Applies to: =$B4:$L28

    2nd conditional format:
    FORMAT CELLS BASED ON THEIR VALUES
    Icon Set Custom
    SHOW ICON ONLY (check this box off)
    First icon (green checkmark) when value is > = 1 (type: number)
    Second icon (no icon) when value is > = -1 (type: number)
    Third icon (no icon) when < -1

    Now, when I enter a 1 in B4 or any of the B column cells, it will highlight the entire row for me and replace the "1" with a checkmark.

    BUILT-IN TEMPLATE WITH THIS FORMATTING:
    1) Open Excel, search for a new template. Enter "Inventory" as the search term
    2) Select the template titled "Inventory list with highlighting"
    3) Highlight the first row of the table, open conditional formats to manage/edit. You will see the 2nd and 3rd formats are for highlights and the icon in the B column. You can change the icon to whatever you want. Remove the first format if you don't want the strikeout options from the Discontinued column.
  • To post as a guest, your comment is unpublished.
    ExcelUser · 4 months ago
    I have a question about the initial step of linking the checkbox to a true/false.

    1. Select the first check box in I1, enter formula =$J$1 into the formula bar, and then press the Enter key.

    2. Repeat step 1 until all check boxes are linked to the adjacent cells.

    For the repeat, does it have to be done for each cell or can you get the drag down to autofill? Right now, when I drag down the corner box it will autofill with =$J$1 for everything so that if I check one box, every box is checked. How can I fix this without manually linking each checkbox?
    • To post as a guest, your comment is unpublished.
      crystal · 4 months ago
      Hi,
      The Fill Handle can't help in this case. You need to manually link each checkbox to its adjacent cell.