VBA Blogs: Dynamically Highlight the Row

Welcome back to the VBA blog! This week we are going to expand our learning of how to highlight selected cells in a worksheet from last week.

Today, we are going to use the joint effort of VBA script and conditional formatting to highlight the row of selected cells. It is useful when we have a large dataset and wish to check the data of specific rows. This VBA project will highlight the row of selected cell and easy for user to locate the data.

We would like to add highlight rows for the dataset below:

and the result would be like this:

The first step is to add a new SelectionChange event in the target worksheet.

In VBA IDE, choose the target worksheet (Sheet1 (Test) in this case) and select Worksheet and SelectionChange options as highlighted below.

The editor will create a Private Sub-procedure automatically. In the coding area, we create a new named range ActiveRow to the workbook. This new named range refers to the active cell by selection in a dynamic way.

Then for the next step, go to the conditional formatting Ribbon and add a new rule. We use a formula to determine the cells to format. In the formula input, write the function

The role of this function is to determine if the active row is the same as the row of current selection. If so, the row will follow the conditional formatting as required.

In our case, we use the grey colour fill for the cells following the formula setup in conditional formatting:

Then in the Conditional Formatting Rules Manager, set up the area of the rule to be applied. In this case, we apply the rule to Columns A to O.

Click OK and you should now have the following:

With this method, we can easily locate the data as required.

See you next week for more VBA tips!