If you want to highlight the row and column of the active cell, the below VBA code will help you.
So, we will be using the Excel Worksheet Event Handler. For a worksheet event work, the code must reside in the worksheet object, not a normal module. The event will work only on that sheet in which it is written.
For the row and column of active cell highlight, we will use the selection change event.
The Logic:
The logic is simple. We want to color the entire row and column of the selection. When the selection changes, the color of the previous column and row should vanish.
The code to highlight the row and column of the current selection:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = xlColorIndexNone Target.EntireColumn.Interior.ColorIndex = 37 Target.EntireRow.Interior.ColorIndex = 37 Target.Interior.ColorIndex = xlColorIndexNone End Sub
How does it work?
First of all, we use the Worksheet_SelectionChange event, that triggers whenever you change the selection of cell on the code containing sheet.
Next, we clear all the cell colors from this sheet using the line Cells.Interior.ColorIndex = xlColorIndexNone. (Yes, this code will clear all the colors you have done on the sheet, Except Conditional formatting, as in the gif above.
Then we color the entire column and row using the below lines.
Target.EntireColumn.Interior.ColorIndex = 37
Target.EntireRow.Interior.ColorIndex = 37
Finally, clear the color of the active cell using the line Target.Interior.ColorIndex = xlColorIndexNone.
Now each time when you move your cursor to a new cell, all the colors from the entire sheet. Then the row and columns will be highlighted with the specified color index, except the active cell itself.
Uses of highlighting the column and row of active cell
The best use is to easily locate related information of a cell in a structured table.
The disadvantage of the above code
The above code will wash off all other color formattings. Only conditional formatting will be retained.
So yeah guys, this was the simplest way to highlight the row and column of the active cell using VBA. There are other methods too. You can read about them in the related articles below.
If you have any doubts or any other special requirement related to this article or any other excel VBA related article, ask in the comments section below.
Download the working file below. You can delete the content of the sheet and use as your own.
Related Articles:
Using Worksheet Change Event To Run Macro When any Change is Made | So to run your macro whenever the sheet updates, we use the Worksheet Events of VBA.
Run Macro If Any Change Made on Sheet in Specified Range | To run your macro code when the value in a specified range changes, use this VBA code. It detects any change made in the specified range and will fire the event.
The Worksheet Events in Excel VBA | The worksheet event are really useful when you want your macros run when a specified event occurs on the sheet.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make your work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
Does using this retain the use of the Undo stack, or does it disable it like most conditional formatting VBA code?