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.

Excel VBA to Click One Cell and Highlight Another

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.

Excel VBA to Click One Cell and Highlight Another

  • 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.

Excel VBA to Click One Cell and Highlight Another

  • 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

Excel VBA to Click One Cell and Highlight Another

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.

Excel VBA to Click One Cell and Highlight Another

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

Excel VBA to Click One Cell and Highlight Another

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

Excel VBA to Click One Cell and Highlight Another


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

Excel VBA to Click One Cell and Highlight Another

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.

Excel VBA to Click One Cell and Highlight Another

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

Excel VBA to Click One Cell and Highlight Another


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

Excel VBA to Click One Cell and Highlight Another

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.

Excel VBA to Click One Cell and Highlight Another

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

Excel VBA to Click One Cell and Highlight Another

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

Excel VBA to Click One Cell and Highlight Another

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

Excel VBA to Click One Cell and Highlight Another

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


Similar Readings


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.

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

  • 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

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

  • 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.

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

  • 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:
=OR(ROW()=ActiveR, COLUMN()=ActiveColumn)
  • 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

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo