Print Date: 2024-09-12

Concept Map

This concept map provides a simple explanation of the use of Excel VBA with examples.

Highlighting Row of Select Cell

I demonstrate how to begin using VBA through one of my primary use cases: highlighting the row of the active, selected cell. In this example, I use a Parkinson’s Glossary Map XLSM file1. The code is also found in Sheet 2 of the Workbook.

VBA Code:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Static xRow

If xRow <> “” Then With Rows(xRow).Interior .ColorIndex = xlNone End With End If

Active_Row = Selection.Row xRow = Active_Row With Rows(Active_Row).Interior .ColorIndex = 20 .Pattern = xlSolid End With

End Sub

Instruction to Highlight Active Row:

  • Copy Text
  • Right Click Desired Sheet (tab)
  • View Code
  • Insert Code
  • Close Window

Step 1 - Copy Code

Copy code from the this file or from Cell B2 in Sheet “Highlight Active Row”

Step 2 - View Code

Right Click Selected Sheet, then select View Code

Step 3 - Paste Code

Paste code in the Selected Project Sheet. You’ll find all open Excel Workbooks on the left side Project - VBA Project menu

Step 4 - Done

Selected Cell is on Cell C1 of Row 1.

Considerations

Keep things in mind:

  • Save your file as .xlsm instead of .csv or .xlsx to keep the VBA code intact after closing the workbook. You will generally receive a notification regarding this matter.

  • This specific code highlights the row of the active cell when a selection change occurs, as indicated in the code. This is applied to whichever sheet(s) you manually choose.

    • You will need to make a few changes to the code to apply it to all sheets in the workbook if you prefer. For that, just ask AI for assistance. It goes without saying–though it’s being said–that you should be careful not to enter PHI or other private information.

  • Caution: This particular code does override any filled-in colors (e.g., Column C) applied via the Fill Color Paintbox icon (blue), leaving the row of the selected cell without any filled-in colors after exiting and selecting outside that row. However, this code does not override Conditional Formatting (e.g., Column D and Column F).

It’s possible—most things are—to resolve these issues and improve further, but doing so requires time for exploration.

Selected Cell is on Cell C1 of Row 4, resulting in removed filled-in color from Row 1. Notice the Conditional Formatting colors have not changed.

Selected Cells were on Row 8 thru 11. While the cells in the image are set on Column C, you can choose any cell on that row and the filled-in color will end up being removed.

References

  1. Tham, K. (2024). Excel VBA Example Workbook XLSM. Google Drive. https://drive.google.com/drive/folders/1mbMap3GOIZBJBEfiPq1zVIrV_w4BUXJY?usp=sharing.↩︎