Thứ Tư, Tháng Hai 12, 2025
spot_img
HomeFinance3 ways to highlight cells or rows in Excel

3 ways to highlight cells or rows in Excel

Microsoft Excel comes with several features that allow users to create basic to advanced spreadsheets, making professional life easier. However, when you have to deal with large sets of complex data in Excel, it's easy to lose interest.

An easy way to simplify your spreadsheet is to highlight checkboxes and rows with checkboxes so they stand out. Below, the 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 this article will discuss involves using VBA code. VBA is essentially a programming language in Excel used to automate tasks. You enter the code relevant to your request and Excel will perform the task for you, so you don't have to spend hours doing it manually.

We all know it takes hours in Excel when you have to do everything manually. VBA code processes large amounts of data to make this process easier for you. Follow these steps to continue with this method:

1. Launch Excel and open the targeted worksheet.

2. Right click on the tab Sheet at the bottom and select View Code in the context menu.

Select View Code
Select View Code

3. This will open a dialog box Microsoft Visual Basic for Applications. Copy the code mentioned below and paste it in the VBA window. You can modify the cell range, 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
Microsoft Visual Basic for Applications dialog box
Microsoft Visual Basic for Applications dialog box

Highlight cells or rows using Conditional Formatting

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

Xem thêm  Compare Spotify and Apple Music

In this method, the article will apply a condition that will label the highlighted checkboxes as True and highlight them (this can be modified according to your requirements). The rest of the rows will be in a standard format, making it easier for you to distinguish between completed tasks and unfinished tasks.

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

1. Before applying conditional formatting, you must add a checkbox to the table. To do this, go to the tab Developer in Excel.

2. In section Controlsselect Insert and click the checkbox icon in the section Form Controls.

Click the checkbox icon in the Form Controls section
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 checkboxes to all table cells.

Add checkboxes to all cells
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 dialog box Format Object.

Format Object dialog box
Format Object dialog box

6. Find options Cell link and enter the cell name to which you want to link your first checkbox. For example, the first checkbox of the example is inside the cell H4 and want to label the cell right before it, so the example will add I4 into the text field.

Add I4 to the text field
Add I4 to the text field

7. Click OK to apply the changes and do the same steps for the rest of the checkboxes in the table.

Click OK
Click OK

8. Once done, select the rows you want to highlight and click options Conditional Formatting in tabs Home.

Xem thêm  3 mobile apps provide creative photo effects
Click the Conditional Formatting option
Click the Conditional Formatting option

9. Choose New Rule from the context menu.

Select New Rule
Select New Rule

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

Click Use a formula to determine which cells to format
Click Use a formula to determine which cells to format

11. Now, let's move to the section Edit the Rule Description and enter =IF($I4=TRUE,TRUE,FALSE) into the text field.

Enter =IF($I4=TRUE,TRUE,FALSE)
Enter =IF($I4=TRUE,TRUE,FALSE)

12. Press the button Format and choose highlight color.

13. Then click OK to save changes.

Save changes
Save changes

Selected rows or cells will be highlighted using conditional formatting.

Highlight rows with different colors through 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-do tasks and want to highlight items that are older than 30 and 45 days, you can highlight such items in different colors. Here's how:

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

2. In tabs Home and click options Conditional Formatting.

3. Select New Rules.

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

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 must change the values ​​accordingly.

$C4>20
  Enter formula
Enter formula

6. Click the button Format and choose color.

7. Click OK to save changes.

Click OK to save the changes
Click OK to save the changes

8. Finally, press OK once again. Items that are more than 20 days old will be highlighted.

Xem thêm  4 ways to add friends on Spotify
Highlighted items
Highlighted items

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

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

$C4>35
Enter formula
Enter formula

11. Click the button Format and choose color.

12. Choose OK.

Select OK
Select OK

13. Click OK again to save the changes.

Click OK again
Click OK again

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

Highlighting rows and cells in an Excel spreadsheet can provide many benefits. In addition to bringing your worksheet to life, doing so will make the data easier to see.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -
Google search engine

Most Popular

Recent Comments