How to Highlight with Cursor in Excel: 3 Suitable Methods

Method 1 – Use Conditional Formatting to Highlight Active Row and Column

Steps:

• Select the entire dataset which you want to highlight. 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.
• Click on the ‘Use a formula to determine which cells to format’ box. Type the following formula in the ‘Format values where this formula is true’ box. Click on the Format button.

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

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

• You will see that the 1st row of our dataset is highlighted.

• Highlight another row; just click on any cell of this row and press F9. Here is the result.

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.

• Choose a highlighting color by clicking on the Format option.

• The first column of our dataset is highlighted.

• Highlight a new column; just click on any cell of this column and then press F9. Look at the following image for the result.

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.

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

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

Method 2 – Apply Conditional Formatting Along with a VBA Code

 Steps:

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

• 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

• Click on any cell in your dataset.

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

2.1 Highlight Active Row

After minimizing the VBA window, return to the dataset, and select the whole dataset. Follow the steps below.

 Steps:

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

• Click on the ‘Use a formula to determine which cells to format’ box. Type the following formula in the ‘Format values where this formula is true’ box. Click on Format to choose a formatting color.

Here is the result.

2.2 Highlight Active Column

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

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

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.

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

Method 3 – Use VBA Codes to Highlight with Cursor

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

3.1 Highlight Multiple Rows and Columns with Union Function

Apply the Union function in VBA. This function allows you to select and highlight multiple cells and their corresponding rows and columns.

To apply this magic trick, just follow the steps.

 Steps:

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

• Copy the following codes and paste them into the VBA window of the specific worksheet. 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

• 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 use the same VBA code. If your cells are not next to each other, use CTRL to select multiple cells.

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

Here is the result.

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 only to highlight an active row, just delete the line below,

.EntireColumn.Interior.ColorIndex = 22

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

.EntireRow.Interior.ColorIndex = 27

Download Practice Workbook

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

Related Articles

• How to Move Cursor in Excel Cell
• Excel Cursor Movement: Logical vs Visual
• How to Change Cursor Color in Excel
• [Fixed!] Excel Cursor Locked in Select Mode
• [Fixed!] Excel Cursor Stuck on White Cross

<< Go Back to Cursor in Excel | Excel Parts | Learn Excel