3 ways to highlight cells or rows in Excel

Microsoft Excel comes with a number of features that allow users to create spreadsheets from basic to advanced, making professional life easier. However, when dealing with large, complex data sets in Excel, it is easy to lose interest.

One easy way to simplify your spreadsheet is to highlight checkboxes and rows with checkboxes so that they stand out. The following article will discuss in detail different ways to do this.

Highlight cells or rows using VBA code

To highlight a cell or row in Excel that has a checkbox, the first method we will discuss involves using VBA code. VBA is basically a programming language in Excel that is used to automate tasks. You enter the code relevant to your requirement and Excel will do the task for you, saving you hours of manual work.

We all know that it takes hours in Excel when you have to do everything manually. VBA code handles 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 select View Code from the context menu.

3 ways to highlight cells or rows in Excel
Select View Code

3. This will open the Microsoft Visual Basic for Applications dialog box . Copy the code mentioned below and paste it in the VBA window. You can modify the cell range, height and width as per your requirement.

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
3 ways to highlight cells or rows in Excel
Microsoft Visual Basic for Applications dialog box

Highlight cells or rows using Conditional Formatting

The next method on the list to highlight a cell or row is to use the Conditional Formatting option. Conditional Formatting allows users to modify the appearance of cells based on preferred criteria.

In this method, the article will apply a condition that will label the highlighted checkboxes as True and highlight them (this can be modified as per your requirement). The rest of the rows will be in a standard format, making it easy to differentiate between completed and incomplete tasks.

Here's how you can use Conditional Formatting to highlight a cell or row:

1. Before you can apply conditional formatting, you must add check boxes to your table. To do this, go to the Developer tab in Excel.

2. In the Controls section , select Insert and click the check box icon in the Form Controls section.

3 ways to highlight cells or rows in Excel
Click the checkbox icon in the Form Controls section

3. Add checkboxes to the cells you want.

4. Then, select the cell with the checkbox and drag the cursor to the bottom of the table. This will add the checkbox to all cells in the table.

3 ways to highlight cells or rows in Excel
Add checkboxes to all cells

5. Now, right-click on the first cell with the checkbox and select Format Control from the context menu. This will launch the Format Object dialog box.

3 ways to highlight cells or rows in Excel
Format Object dialog box

6. Find the Cell link option and enter the name of the cell you want to link your first checkbox to. For example, our first checkbox is in cell H4 and we want to label the cell right before it, so we'll add I4 to the text field.

3 ways to highlight cells or rows in Excel
Add I4 to text field

7. Click OK to apply the changes and perform the same steps for the rest of the check boxes in the table.

3 ways to highlight cells or rows in Excel
Click OK

8. Once done, select the rows you want to highlight and click on the Conditional Formatting option in the Home tab.

3 ways to highlight cells or rows in Excel
Click on the Conditional Formatting option

9. Select New Rule from the context menu.

3 ways to highlight cells or rows in Excel
Select New Rule

10. In the following dialog box, click Use a formula to determine which cells to format in the Select a Rule Type option .

3 ways to highlight cells or rows in Excel
Click Use a formula to determine which cells to format

11. Now go to Edit the Rule Description section and enter =IF($I4=TRUE,TRUE,FALSE) in the text field.

3 ways to highlight cells or rows in Excel
Enter =IF($I4=TRUE,TRUE,FALSE)

12. Click the Format button and choose a highlight color.

13. Then click OK to save the changes.

3 ways to highlight cells or rows in Excel
Save changes

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

Highlight rows with different colors using Conditional Formatting

Sometimes you want to highlight different rows with different colors to better organize your spreadsheet. Luckily, Conditional Formatting allows you to do that too.

For example, if you are listing your to-dos and want to highlight items that are due more than 30 and 45 days in duration, you can highlight such items in different colors. Here's how:

1. Select the rows in the table that you want to highlight (usually the entire data set).

2. In the Home tab and click the Conditional Formatting option.

3. Select New Rules.

4. In the following dialog box, select Use a formula to determine which cells to format in the Select a Rule Type section .

5. Enter the following formula in the text field for Format values ​​where this formula is true . Remember that the article is applying the formula according to the example table. To make this method work in your case, you will have to change the values ​​accordingly.

$C4>20
3 ways to highlight cells or rows in Excel
Enter formula

6. Click the Format button and choose a color.

7. Click OK to save the changes.

3 ways to highlight cells or rows in Excel
Click OK to save changes

8. Finally, click OK again. Items older than 20 days will be highlighted.

3 ways to highlight cells or rows in Excel
Highlighted items

9. Now, select the New Rule button again and click Use a formula to determine which cells to format under Select a Rule Type .

10. Enter the following formula in the Format values ​​where this formula is true text field .

$C4>35
3 ways to highlight cells or rows in Excel
Enter formula

11. Click the Format button and choose a color.

12. Select OK.

3 ways to highlight cells or rows in Excel
Select OK

13. Click OK again to save the changes.

3 ways to highlight cells or rows in Excel
Click OK again

This will help you highlight rows and cells with different colors in Excel. If you want to cross something out in your spreadsheet, you can strikethrough it in Excel.

Highlighting rows and cells in an Excel spreadsheet can have many benefits. In addition to bringing your spreadsheet to life, doing so makes your data easier to see.

Sign up and earn $1000 a day ⋙

Leave a Comment

Ways to reduce the risk of birth defects in the fetus

Ways to reduce the risk of birth defects in the fetus

Birth defects are something no one wants. Although they cannot be completely prevented, you can take the following steps to reduce the risk of birth defects in your baby.

What planets are Venus and Venus?

What planets are Venus and Venus?

What are the evening star and the morning star? Here's what you need to know about the evening star and the morning star.

Huawei Mate XT Ultimate screen repair price is as expensive as an iPhone 16 Pro Max

Huawei Mate XT Ultimate screen repair price is as expensive as an iPhone 16 Pro Max

According to the official price list announced by Huawei itself, repairing the Mate XT Ultimate screen will cost up to 7,999 CNY, equivalent to 1,123 USD or nearly 28 million VND, equal to the price of an iPhone 16 Pro Max.

Summit supercomputer is about to retire

Summit supercomputer is about to retire

Oak Ridge National Laboratory (ORNL) announced that Summit, the world's most powerful supercomputer in 2018 and 2019, will be shut down in November after nearly six years of operation.

How to connect wifi on Panasonic Smart TV

How to connect wifi on Panasonic Smart TV

Most Panasonic smart TVs support wifi connection so that users can use and experience many useful features, programs and information not only domestically but also abroad.

How to connect wifi for LG Smart TV

How to connect wifi for LG Smart TV

Connecting wifi to LG Smart TV is not too difficult, you just need to follow 4 steps according to the instructions in our article to succeed.

How to connect wifi for Sony Smart TV

How to connect wifi for Sony Smart TV

If you are looking for a way to connect wifi to your Sony Smart TV, please refer to this article of ours!

What order should I watch Attack on Titan?

What order should I watch Attack on Titan?

What is the order to watch Attack on Titan is a question that many people are interested in. This article will tell you how many parts Attack on Titan has and how to watch it.

Thai Alphabet

Thai Alphabet

If you love Thailand and want to learn Thai, the first thing you need to do is get acquainted with and memorize the Thai alphabet.

OpenAI Announces GPT-4.1 - The Smartest Model for Complex Tasks

OpenAI Announces GPT-4.1 - The Smartest Model for Complex Tasks

OpenAI has officially introduced three new models: GPT-4.1, GPT-4.1 mini, and GPT-4.1 nano. These models come with a massive context capacity of up to 1 million tokens and a knowledge limit updated until June 2024.

How to present survey results in PowerPoint or Google Slides

How to present survey results in PowerPoint or Google Slides

You have the survey results in hand but don't know how to present them? Don't worry, this article will guide you how to present the survey in PowerPoint, Google Slides.

200+ first sight words for children learning English

200+ first sight words for children learning English

Sight words are short, common, “easy” words that young children can learn to recognize by seeing them (rather than sounding them out). Here are the first English sight words that children should learn.

Simple Mobile Tools Resold, Putting Companys Android Apps at Privacy Risk

Simple Mobile Tools Resold, Putting Companys Android Apps at Privacy Risk

The latest example of this is the sale of the Simple Mobile Tools suite to ZipoApps, turning once-useful Android apps into potential privacy risks.

Amazon Tribe Finally Connects to the Internet and the Unexpected Consequences

Amazon Tribe Finally Connects to the Internet and the Unexpected Consequences

The reclusive Marubo tribe in the Amazon jungle was connected to the outside world by Elon Musk's Starlink satellite internet service nine months ago. However, the emergence of this technology has brought unexpected consequences.

Unlocking Baccarats Social Thrills: Tedbets Global Gaming Hub

Unlocking Baccarats Social Thrills: Tedbets Global Gaming Hub

Discover the vibrant social scene of baccarat on Tedbet, where players worldwide connect, chat, and enjoy the excitement of live dealer games.