Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Highlight with Cursor in Excel (3 Suitable Ways)

If you are looking to highlight the cursor position before entering data into the cell or working with a lot of data are at risk of losing track of where your cursor is, then you are in the right place. In this tutorial, you will learn 3 useful ways to highlight rows, columns, or both with the cursor in Excel.


Download Practice Workbook

You can download the following practice workbook that we have used to prepare this article.


3 Methods to Highlight with Cursor in Excel

Our main goal is to highlight a row or column or both row and column simultaneously with the cursor. We will use the following dataset to describe the whole article.

3 Methods to Highlight with Cursor in Excel


1. Use Conditional Formatting to Highlight Active Row and Column

If you want to highlight the selected row or/and column without using any VBA code, the best you can do is use conditional formatting. After applying the formula in conditional formatting, the first cell of our selected dataset is highlighted. Then, if you select another cell and press F9, the sheet is refreshed, the formula is recomputed, and the highlighting is updated.

This method is not as smooth as the VBA method (discussing next) as it requires recomputing the sheet manually. However, if you don’t want to use a macro-enabled Excel file, use one of the following ways as per your needs.


1.1 Highlight Active Row

To highlight the active row, just follow the steps below.

📌 Steps:

  • First, select the entire dataset which you want to highlight. Then go to the Home tab >> Conditional Formatting button under the Styles group >> click on the New Rule. A New Formatting Rule dialog box will appear.

Use Conditional Formatting to Highlight Row and Column of Active Cell with Cursor

  • Click on ‘Use a formula to determine which cells to format’ box. Type the following formula in the ‘Format values where this formula is true’ box. After that, click on the Format button.
=CELL("row")=ROW()

Use Conditional Formatting to Highlight Row and Column of Active Cell with Cursor

  • In the Format Cells dialog box, under the Fill section, choose a highlighting color. Finally, press OK.

Use Conditional Formatting to Highlight Row and Column of Active Cell with Cursor

  • It will return to the New Formatting Rule window again. At this time, just click on the OK.

  • Now, you will see that the 1st row of our dataset is highlighted.

Use Conditional Formatting to Highlight Row and Column of Active Cell with Cursor

  • If you want to highlight another row, just click on any cell of this row and press F9. Here is the result.

Use Conditional Formatting to Highlight Row and Column of Active Cell with Cursor


1.2 Highlight Active Column

To highlight an active column, repeat the steps above except the formula putting step.

  • Copy the following formula and paste it into the ‘Format values where this formula is true’ box.
=CELL("col")=COLUMN()
  • After that, choose a highlighting color by clicking on the Format option.

Use Conditional Formatting to Highlight Row and Column of Active Cell with Cursor

  • Here is the result. The first column of our dataset is highlighted.

  • Now, if you want to highlight a new column, just click on any cell of this column and then, press F9. Look at the following image for the result.

Use Conditional Formatting to Highlight Row and Column of Active Cell with Cursor


1.3 Highlight Both Active Row and Column

  • To get both column and row highlighted, you need to write the following formula in the formula box.
=OR(CELL("row")=ROW(), CELL("col")= COLUMN())

Use Conditional Formatting to Highlight Row and Column of Active Cell with Cursor

  • Repeat all the steps as we stated before. You will get results like the following.

Use Conditional Formatting to Highlight Row and Column of Active Cell with Cursor

  • Don’t forget to press the F9 key to apply highlighting after selecting another cell. 👇

Use Conditional Formatting to Highlight Row and Column of Active Cell with Cursor

Read More: How to Change Cursor Color in Excel (2 Quick Methods)


2. Apply Conditional Formatting Along with a VBA Code

To get rid of slowing down your workbook and recalculating a worksheet on every move, you can use VBA code to get the active row and column number, then put this number to the ROW and COLUMN function used in the conditional formatting formulas.

Follow the steps below to get the active row and column number using VBA codes. Before going, just open a new worksheet in your current workbook. And name that current worksheet as PartnerSheet.

📌 Steps:

  • First, right-click on the sheet name >> click on the View Code. A module window will pop up.

Highlight Active Row and Column Number Applying Conditional Formatting Along with a VBA Code

  • In the module window, write down the following formula.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Worksheets("PartnerSheet").Cells(3, 2) = Target.Row
Worksheets("PartnerSheet").Cells(3, 3) = Target.Column
Application.ScreenUpdating = True
End Sub

Highlight Active Row and Column Number Applying Conditional Formatting Along with a VBA Code

  • Now, click on any cell in your dataset.

You can also see the row and column number of that cell in the PartnerSheet.

Highlight Active Row and Column Number Applying Conditional Formatting Along with a VBA Code


2.1 Highlight Active Row

After minimizing the VBA window, go back to the dataset, and select the whole dataset. Now, follow the steps below.

📌 Steps:

  • Go to Home tab >> Conditional formatting >> New Rule.

Highlight Active Row

  • Click on ‘Use a formula to determine which cells to format’ box. Type the following formula in the ‘Format values where this formula is true’ box. After that, click on Format to choose a formatting color.
=ROW()='PartnerSheet'!$B$3

Highlight Active Row

Finally, here is the result.

Highlight Active Row


2.2 Highlight Active Column

To highlight a column, type the following formula in the formula box.

=COLUMN()='PartnerSheet'!$C$3

Highlight Active Column

Now click on any cell to see the entire highlighted column of this cell.

Highlight Active Column


2.3 Highlight Active Row and Column Both

If you want to highlight both row and column simultaneously, copy the following formula and paste it into the formula box as shown in the following image.

=OR(ROW()='PartnerSheet'!$B$3, COLUMN()='PartnerSheet'!$C$2)

Highlight Active Row and Column Both

Finally, here is the result. Just click on any cell that you want to highlight both row and column of that cell.

Highlight Active Row and Column Both

Read More: Excel Cursor Movement: Logical vs Visual


Similar Readings


3. Use VBA Codes to Highlight with Cursor

If you are comfortable with VBA codes in Excel, then, there are available very efficient VBA codes that can help you to highlight both rows and columns in one click. We have discussed two VBA codes here for you to highlight with cursor in Excel.

3.1 Highlight Multiple Rows and Columns with Union Function

In the first code, we will apply the Union function in VBA. This code will allow you to select and highlight multiple cells and their corresponding rows and columns.

To apply this magic trick, just follow the steps.

📌 Steps:

  • First, right-click on the sheet name (where your dataset can be found), and select View Code. A module window will pop up.

Apply VBA Codes to Highlight with Cursor

  • Now, copy the following codes and paste them into the VBA window of the specific worksheet. After that, minimize the window.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = 0
Union(Target.EntireColumn, Target.EntireRow).Interior.ColorIndex = 34
End Sub

Apply VBA Codes to Highlight with Cursor

  • Click on any cell of your dataset. You will see the entire row and column of that cell highlighted.

If you want to highlight multiple rows and columns by selecting multiple cells, you can do this by using the same VBA code. If your cells are not next to each other, then use CTRL for selecting multiple cells.

Apply VBA Codes to Highlight with Cursor


3.2 Highlight Single Row and Column

This code will highlight only one row and one column at a time.

📌 Steps:

  • Please, copy the following code and paste it into the VBA module (select your sheet from the Microsoft Excel Objects menu).
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
With Target
.EntireRow.Interior.ColorIndex = 27
.EntireColumn.Interior.ColorIndex = 22
End With
Application.ScreenUpdating = True
End Sub

VBA Code for Highlighting Single Row and Column

Finally, here is the result.

VBA Code for Highlighting Single Row and Column

You can customize this code for color, in the following ways.

  • The code uses 2 different colors, color index 27 for the row and 22 for the column. If you want to customize the color, just change the ColorIndex codes
  • If you want to highlight the row and column with the same color, use the same color index.
  • If you want to only highlight an active row, just delete the line below,

.EntireColumn.Interior.ColorIndex = 22

  • If you want to only highlight an active column, just delete the line below,

.EntireRow.Interior.ColorIndex = 27

Read More: How to Move Cursor in Excel Cell (2 Useful Methods)


Conclusion

In this tutorial, I have discussed 3 useful ways to highlight rows, columns, or both with the cursor in Excel. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Hafiz Islam

Hafiz Islam

Hi there. I am Hafiz, graduated from BUET. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. Now you can see my articles in the ExcelDemy blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo