VBA Blogs: Dynamically Highlight the Entire Row and Column by Using VBA Script

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’s article.

Last week, we have used both VBA script and conditional formatting to highlight the row and columns of selected cells. This week, we are going to create the VBA script to achieve the same effect as last week’s blog.

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

The result would look like this:

The first step is to add a new SelectionChange event in the target worksheet as described  previously, together with some relevant variables.

Then, we use the statement On Error Resume Next. As one of most used error-handling routines, it allows execution to continue despite a run-time error.

We delete the current formatting in the cells of the target worksheet (If any) and assign the colour index to the variable iColor:

Then we use the With statement to locate the target cell’s entire row formatting.  Here, we delete the target cell formatting, add format condition type parameter xlExpression, which specifies whether the conditional format is based on a cell value or an expression, and then assign the colour index to the target cell’s interior colour index.

Next, we apply the same coding logic to the target cell’s entire column formatting as the code indicated below:

Combing all the lines of code together, we get this:

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

See you next week for more VBA tips!