How to Shade Every Other Line in Excel (With Examples & VBA)

Shading every other row in Excel makes large datasets easier to read. This is called "zebra striping." There are several ways to do this:

1. Using Conditional Formatting (Easy, No Code)
2. Using Excel Table Styles (Quick and Dynamic)
3. Using VBA Code (For Automation)

Let’s explore each method step by step.

Method 1: Using Conditional Formatting

1. Select the range where you want alternate row shading.
2. Go to Home → Conditional Formatting → New Rule.
3. Choose “Use a formula to determine which cells to format.”
4. Enter this formula:

   =MOD(ROW(),2)=0

   • This formula checks if the row number is even. You can change =0 to =1 to shade odd rows instead.
5. Click Format, go to the Fill tab, and choose a color.
6. Click OK → OK again, and your alternating row shading will be applied dynamically.

Pros:
✔ Works dynamically (updates as you add/delete rows)
✔ No VBA required

Method 2: Using Excel Table Styles

If your data is in a table, Excel can automatically apply alternating row colors.

1. Select your data range.
2. Press Ctrl + T or go to Insert → Table.
3. Check "My table has headers" (if applicable) and click OK.
4. Go to Table Design (Appears when the table is selected).
5. Check the "Banded Rows" option.
6. Choose a style from the Table Styles section.

Pros:
✔ Quick and easy
✔ Automatically updates with new data

Cons:
✖ Only works within a formatted table

Method 3: Using VBA for Automation

A VBA macro can automate shading every other row if you need it often.

VBA Code to Shade Every Other Row

Sub ShadeEveryOtherRow()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim lastRow As Long
    Dim rowNum As Integer
    
    ' Set the active worksheet
    Set ws = ActiveSheet
    
    ' Find the last used row in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Set the range to apply shading
    Set rng = ws.Range("A2:Z" & lastRow) ' Adjust columns as needed
    
    ' Loop through each row in the range
    For Each cell In rng.Rows
        rowNum = cell.Row
        ' Check if the row is even
        If rowNum Mod 2 = 0 Then
            cell.Interior.Color = RGB(220, 230, 241) ' Light blue shading
        Else
            cell.Interior.ColorIndex = xlNone ' No fill for odd rows
        End If
    Next cell
    
    MsgBox "Shading Applied!", vbInformation, "Done"
End Sub

How to Use This VBA Code

1. Press Alt + F11 to open the VBA Editor.
2. Click Insert → Module.
3. Copy and paste the above VBA code.
4. Close the editor and return to Excel.
5. Press Alt + F8, select ShadeEveryOtherRow, and click Run.

Pros:
✔ Automates the process
✔ Can apply to multiple sheets

Cons:
✖ Requires enabling macros

Conclusion

• If you want a quick, dynamic solution, use Conditional Formatting.
• If you're working with a structured table, use Table Styles.
• If you frequently apply shading to large datasets, use VBA for automation.