VBA Blogs: Dynamically highlight entire row and column

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.

Last week, we have used both VBA script and conditional formatting to highlight the row of selected cells. This week we are going to use the VBA script and conditional formatting to highlight both the row and column of selected cells. It is an enhancement of the method we used last week and it will highlight the row of selected cell and easy for user to locate the data.

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

and the result would look like this:

The first step is to add a new SelectionChange event in the target worksheet as described in last week blog.

In the coding area of sub-procedure, we create two new named ranges ActiveRow and ActiveColumn to the workbook the same way as described last week. The new named ranges refer to the active cell by selection. Therefore, the reference here is dynamic.

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 and active column is the same as the row and column of current cell selection. If so, the row and column 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!