Highlight Alternate Rows in Selection Using Macros In Excel

I’ve always believed Excel is more than just a grid of numbers — it’s a powerhouse of productivity if you know how to bend it to your will. Over the years, I’ve used formulas, pivot tables, conditional formatting, and all sorts of tricks to make my data easier to read and manage. But there’s one small thing that makes a huge visual difference: highlighting alternate rows.

Sure, Excel has built-in table styles and conditional formatting to do this. But what if you want more control? What if you want the effect to apply only to a specific selection rather than the entire sheet? Or maybe you want it to trigger instantly without manually setting up rules each time. That’s where VBA macros come into play.

In this article, I’ll walk you through exactly how I highlight alternate rows in any selected range using a macro.

Key Takeaways:

• Macros allow fast, one-click highlighting of alternate rows in any selected range.
• VBA gives more control and flexibility compared to conditional formatting.
• The “Note” style macro is quick and easy to implement.
• RGB color-based macros allow full customization of highlight colors.
• Save your file as .xlsm to preserve and run macros in the future.

 

 

Why I Chose the Macro Approach

Let’s get one thing straight — Excel’s Format as Table button and conditional formatting with formulas can absolutely highlight alternate rows. But here’s why I personally prefer the macro approach for certain projects:

• It’s faster for ad-hoc tasks. I can select any random range, run my macro, and boom — alternate rows are highlighted instantly.
• No hidden formatting rules. Conditional formatting sometimes creates extra rules in the background, which can bloat a file or behave unpredictably when copying data.
• Easy customization. I can quickly tweak the macro for colors, patterns, or even apply multiple alternating colors in one go.
• Better for non-table ranges. Sometimes I’m working on a section of a sheet that isn’t a structured table, and I just want the highlight applied temporarily.

Think of it like using a custom paintbrush rather than a pre-painted stencil — more freedom, more control.

Highlight Alternate Rows in Selection Using Macros

Setting the Stage: Preparing Excel for Macros

Before I could dive into writing code, I had to make sure Excel was set up to let me use macros. If you’ve never touched VBA before, here’s what I did:

• Enabled the Developer Tab – I went to File → Options → Customize Ribbon and checked the “Developer” box. This gave me quick access to the VBA editor and macro tools.

• Checked Macro Security Settings – Under Developer → Macro Security, I ensured macros were enabled (or at least set to “Disable with Notification” so I can allow them when I need).

• Opened the VBA Editor – With the Developer tab ready, I hit Alt + F11 to launch the Visual Basic for Applications editor.

At this point, I had a blank slate ready for some code magic.

Write VBA Code

Here is our initial set of data:

STEP 1: Go to Developer > Code > Visual Basic

STEP 2: Paste in your code and Select Save. Close the window afterwards.

Sub HighlightAlternateRowsInSelection()
Dim range As Range
For Each range In Selection.Rows
'Check if it's the alternate row by using modulo, set it to the style of note
If range.Row Mod 2 = 1 Then
range.Style = "Note"
Else
End If
Next range
End Sub

STEP 3: Let us test it out!

Open the sheet containing the data. Go to Developer > Code > Macros

Make sure your data and macro are selected. Click Run.

With just one click, the alternate rows of your selection are now highlighted!

 

Pro Tips I’ve Learned

After using and tweaking these macros for years, here are a few lessons:

• Screen Updating Off = Faster – For big ranges, turn off screen updating before the loop: Application.ScreenUpdating = False ‘ Your code here Application.ScreenUpdating = True
• Undo Isn’t Built In – VBA actions can’t be undone with Ctrl+Z. If I need a safety net, I copy my sheet first.
• Avoid Merged Cells – Merged cells mess up row-by-row formatting. If possible, unmerge before running.
• Save as XLSM – Always save macro-enabled workbooks as .xlsm so the macro code sticks around.

 

FAQs