How to Highlight Active Columns and Rows in Excel?

In this Excel tutorial, we will learn how to highlight active columns and rows in Excel. If you have a lot of data, you can easily find dynamic columns or rows if the rows or columns can be highlighted.

You need to write some code in Visual Basic to do this, although we will do that later. However, the problem is that the Visual Basic code is not saved in xls or xlsx type files. It actually turns on the macro.

So you have to save the Excel file in .xlsm (Excel Macro-Enabled Wordbook) file type at the beginning, or when you write Visual Basic code, you have to change the File Type from Save As .xlsm type.

How to Highlight Active Columns and Rows in Excel?

How to Use Excel Conditional Formatting?

1. Now open the file and select the cell range of the seat you will be working on.
2. Once selected, click on New Rule from Conditional Formatting from the ribbon of the Home tab.
3. Clicking on New Rule will bring up the “New Formatting Rule” dialog box as below.
4. Click on Use a formula to determine which cells to format from the dialog box. The image above is highlighted. Now it will come as below.
5. Now in the highlighted cell, we will write the following formula. And the formatting will be set depending on this condition.
6. Now click on the Format button.
7. After clicking on the highlighted Format button, the Format Cells dialog box appears.
8. Now format it as you wish from here and click on the OK button. Now you will see that the first column or the column and row of the cell from where you started has taken the formatting you have given.
9. Now, if you click on any place in the data set, formatting is usually supposed to be applied there. But in this case, you have to press F9 of the function key.
10. Now we will see how to auto do this so that F9 is not pressed repeatedly.

How to Use Developer Tab in Excel?

However, in many cases, there is no Developer Option. Follow the steps below to turn it on.

• File> Options> Customize Ribbon> Developer

How to do Visual Basic (VB) for Automating Conditional Formatting?

1. Now you need to write some basic visual code in Excel. And to save it, you have to save the file in .xlsm type, which we have discussed earlier.
2. So to turn on Visual Basic (VB for short), go to the Developer ribbon from the menu and click on Visual Basic (keyboard command Alt + F11). The “Microsoft Visual Basic for Application”(VBA) window will appear as follows.
3. Now double click on sheet1 on the left side of the seat you are working on. The code editor will appear as below.
4. Select Worksheet here. Then Private Sub Block will come as below.
5. Now it’s time to write VB Code to auto column and row.

VB Code for Active Column and Row Auto

Now in Private Sub Block, You will write the following code.

Private Sub Worksheet_SelectionChange (ByVal Traget As Rang)
If Application.CutCopyMode = False Then
Application.Calculate
End If
End Sub

Now click on the different cells inside the excel sheet and see that the active columns and rows are being auto highlighted. So this was our event today about How to Highlight Active Columns and Rows in Excel. Don’t forget to share it with others at work and Subscribe to our newsletter.