How to Click One Cell and Highlight Another in Excel (2 Methods)

In this article, we will learn to click one cell and highlight another in Excel. In excel, often we need to click one cell and highlight another cell that contains the relative data. Most of the time, we use this technique to track activities in an excel worksheet. Today, we will use VBA and Conditional Formatting in Excel to click one cell and highlight another.

Download Practice Book

Download the practice book here.

2 Ways to Click One Cell and Highlight Another in Excel

To explain these methods, we will use a dataset that contains information about the sales amount of the first three months of some sellers.

1. Excel VBA to Click One Cell and Highlight Another

In the first method, we will use VBA to click one cell and highlight another. To explain this method, we have modified the dataset a little. We have added the month’s name below the original dataset. So, if we select January, it will highlight the sales amount of Jan.

1.1 Highlight Another Range

We will highlight a range at first and then we will highlight a cell in the next sub-method.

Let’s follow the steps below to learn this method.

STEPS:

• In the first place, go to the Developer tab and select Visual Basic. It will open the Visual Basic window.

• Secondly, select the sheet where you want to apply the VBA. We have selected Sheet2.
• Now, right-click on the mouse. A drop-down menu will occur.
• Select View Code from there. The Code window will appear.

• Thirdly, write the code below in the Code window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Sheets("VBA")
Select Case Target.Address
Case "$C$13"
.Range("C6:C11").Interior.Color = RGB(255, 255, 153)
Case "$C$14"
.Range("D6:D11").Interior.Color = RGB(204, 153, 255)
Case "$C$15"
.Range("E6:E11").Interior.Color = RGB(0, 255, 0)
End Select
End With
End Sub

Here, this code will help us to highlight a range if we select one cell. For example, if we select Cell C13, then it will highlight the range C6:C11. Here, we can also use a single cell instead of a range.

• After that, press Ctrl + S to save the code and close the Visual Basic window.
• Next, if you click Cell C13, it will highlight the values of January.

• Similarly, click Cell C14 to highlight the values of February.

• Again, click Cell C15 to highlight the values of March.

1.2 Highlight Another Cell

To highlight another cell, we will change a few things inside the VBA code. We will use the same dataset here. We will maintain the same steps till opening the Code window.

Let’s follow the steps below.

STEPS:

• Firstly, go to the Developer tab and select Visual Basic. The Visual Basic window will appear.
• Next, double-click on the sheet where you want to apply the VBA code. It will open the Code window.
• To highlight a single cell, write the code below in the Code window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Sheets("VBA")
Select Case Target.Address
Case "$C$13"
.Range("C6").Interior.Color = RGB(255, 255, 153)
Case "$C$14"
.Range("D6").Interior.Color = RGB(204, 153, 255)
Case "$C$15"
.Range("E6").Interior.Color = RGB(0, 255, 0)
End Select
End With
End Sub

Here, we have changed the range. We have selected a single cell instead of a range. You can change the highlighting colors by changing the color code inside RGB().

• Press Ctrl + S to save the code and close the Visual Basic window.
• Now, if you select Cell C13, only Cell C6 will be highlighted.

• Similarly, select Cell C14 and Cell C15 to highlight Cell D6 and Cell E6 respectively.

1.3 Highlight Only One Range at a Time

We can also highlight only one range or cell at a time. In this case, the previously highlighted ranges or cells will be unhighlighted. Here, we will also follow the same steps described in the Sub-Method-1 to open the Code window. Again, we will use the same dataset.

Let’s pay attention to the steps below.

STEPS:

• Go to the Developer tab and select Visual Basic at first. The Visual Basic window will appear.
• Next, double-click on the sheet where you want to apply the VBA code. It will open the Code window.
• After opening the Code window, write the code below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Sheets("VBA")
.Cells.Interior.ColorIndex = xlColorIndexNone
Select Case Target.Address
Case "$C$13"
.Range("C6:C11").Interior.Color = RGB(255, 255, 153)
Case "$C$14"
.Range("D6:D11").Interior.Color = RGB(204, 153, 255)
Case "$C$15"
.Range("E6:E11").Interior.Color = RGB(0, 255, 0)
End Select
End With
End Sub

Here, we have introduced .Cells.Interior.ColorIndex=xlColorIndexNone. It will remove the previously applied color format every time we select our desired cell.

• Press Ctrl + S to save the code and close the Visual Basic window.
• After closing the Visual Basic window, you will see the below results in your dataset. The previously applied color formats are removed.

• Now, if you select Cell C13, the C6:C11 range will be highlighted.

• Similarly, select Cell C14 to highlight the values of February. In this case, only the values of February month are highlighted.

• Finally, select Cell C15 to highlight the values of March.

Read More: How to Select a Range of Cells in Excel (9 Methods)

Similar Readings

• How to Delete a Cell in Excel (4 Easy Ways)
• Excel if One Cell Equals another then Return another Cell
• Selecting Non-Adjacent or Non-Contiguous Cells in Excel (5 Simple Methods)
• Data clean-up techniques in Excel: Filling blank cells
• What is cell in Excel definition

2. Apply Conditional Formatting in Excel to Click One Cell and Highlight Another

Conditional Formatting is also another way to highlight cells in excel. We will use the conditional formatting feature to highlight the active row and column in our excel worksheet. Here, we will use the same dataset.

Let’s observe the steps below to learn this technique.

STEPS:

• In the beginning, go to the Developer tab and select Visual Basic. The Visual Basic window will appear.

• Secondly, Secondly, select the sheet where you want to apply the VBA. We have selected Sheet3.
• Right-click on the mouse. A drop-down menu will occur.
• Select View Code from there. The Code window will appear.

• Now, write the code in the Code window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ThisWorkbook.Names.Add "ActiveR", ActiveCell.Row
ThisWorkbook.Names.Add "ActiveColumn", ActiveCell.Column
End Sub

• Press Ctrl + S to save the code and close the Visual Basic window.
• After that, select the cells where you want to apply conditional formatting to highlight active rows and columns.

• Next, go to the Home tab and select Conditional Formatting. A drop-down menu will occur.

• Select New Rule from the drop-down menu.

• After selecting the New Rule, the Edit Formatting Rule window will appear.
• Select Use a formula to determine which cells to format.
• Then, type the formula in the Format values where this formula is true field:

• After that, select Format.

• After selecting Format, the Format Cells window will occur.
• Select Fill and choose a color that you want to use to highlight cells.
• Click OK to proceed. Also, click OK in the Edit Formatting Rule window.

• Finally, if we select Cell C7, then Row 7 and Column C will be highlighted.

• Similarly, if we select Cell E11, then Row 11 and Column E will be highlighted.

Read More: Multiple Excel Cells Are Selected with One Click (4 Causes+Solutions)

Things to Remember

There are certain things we need to remember when we want to click one cell and highlight another.

• As there is no default feature for this, we need to use VBA for this. We can easily manipulate the VBA code. Here, we can use it to highlight ranges or a single cell.
• We need to select the cells where we want to apply the formatting before managing the rule. You can also select all the cells by clicking the Select All button.

Conclusion

We have discussed two easy and quick ways to click one cell and highlight another in excel. I hope these methods will help you to solve your problems. Furthermore, we have also added the practice book at the beginning of the article. You can also download the practice book to exercise more. Last of all, if you have any queries or suggestions, feel free to ask in the comment section below.

Related Articles

• How to Move All Cells Down in Excel (5 Methods)
• How to Lock Cells in Excel When Scrolling (2 Easy Ways)
• [Fix]: Arrow Keys Not Moving Cells in Excel (2 Methods)
• How to Move a Group of Cells in Excel (4 Easy Ways)
• Select All Cells with Data in Excel (5 Easy Methods)
• How to Group Cells in Excel (6 Different Ways)