Highlight Row & Column of Selected Cell using VBA

When looking at a big table of analysis (or data), it would make our life simpler if the selected cell’s column and row are highlighted, so that we can instantly compare and get a sense of things. Like this:

Who doesn’t like a little highlighting. So lets learn how to do highlighting today.

Step 1: Identify the area for highlighting

This is simple, unless you are AUI (analyzing under influence). Lets assume that we are dealing with a range of cells in B4:I14

Step 2: Use 2 cells to capture the selected row & column details

Outside our highlight range, lets set aside 2 cells (E17 & E18 in this case) for keeping the details of which row & column needs to be highlighted.

We can call these cells selRow & selCol.

Step 3: Unleash the VBA magic

• Right click on the sheet name & choose ‘view code’.
• Choose Worksheet & Selection Change from the drop-downs.
• Excel would add a blank Worksheet_SelectionChange() sub
• Write the below lines of code.
  • [selRow] = Target.Row
  • [selCol] = Target.Column
• Done. So much simpler than using floo network or transmogrifying muggles.

Step 4: Add conditional formatting to highlight selected cell’s row & column

Now that we know which row & column should be highlighted, it is a simple matter of switching on Excel’s highlighting charm – Conditional Formatting.

Select the entire range (B4:I14) and go to conditional formatting > new rule

Select the rule type as Use a formula… and use a below rules.

• =ROW(B4) = selRow
• Apply formatting
• Repeat the steps & this time use the rule =COLUMN(B4) = selCol

Step 5: Show off.

Incorporate this technique in to your dashboard or weekly report. Watch the socks knocked off your boss’. Bask in the glory. Repeat and enjoy.

Bonus Tip: Use similar technique to enhance user inputs

You can use similar idea to conditionally show messages on your worksheets. See this demo.

I am not telling you how to do this. But I know you are awesome enough to figure this out.

Download Example File

Click here to download example file & understand how to use this technique.

Do you use highlighting techniques in your reports & analysis?

I always use conditional formatting & light-weight VBA to enhance my dashboards and analysis. Especially conditional formatting is almost a magical way to make stunning reports & show off things that are important.

What about you? Do you use these techniques often? what is your experience like? Please share your tips & ideas using comments. I am all ears.

Transmogrify your boring work to awesome – Check out!

• Interactive sales chart using Excel
• Show details on demand using simple VBA
• Highlight points in scatter & line charts
• Display alert symbols in dashboards to grab attention
• Highlight top 10 values using conditional formatting

Also see introduction to conditional formatting & VBA to understand to get the basics right.

For more potent magic, please consider joining our Online VBA Classes.  You are going to leave everyone spellbound.