How to shade rows alternately in all sheets but two

Refugar

New Member
Joined
Jan 31, 2025
Messages
1
Office Version
  1. 2021
Platform
  1. Windows
Hi!
I have an Excel book with more than 30 sheets. I want to shade rows alternately to blue and white colors, starting on row 6 till the last row containing data, but only applied to columns from A to O and excepting sheets named "Protection" and "Help". Also, I want to know where to place the code.
I need a code from scratch you might suggest or you to correct the ones I show below.

Code 1 to be corrected

VBA Code:
Sub TwoColorsForRangeOfRowsAndColumns()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 Dim LastRow As Long
 LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 Dim x As Long
   For x = 6 To LastRow Step 2
     Range(Cells(x, 1), Cells(x, 15)).Interior.ColorIndex = 20
     Next x
  For x = 7 To LastRow Step 2
     Range(Cells(x, 1), Cells(x, 15)).Interior.ColorIndex = 2
     Next x
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
 
Last edited by a moderator:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
VBA Code:
Option Explicit

Sub ShadeAlternateRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim cell As Range
    Dim color1 As Long
    Dim color2 As Long
    Dim startRow As Long
    Dim startColumn As String
    Dim endColumn As String

    color1 = RGB(173, 216, 230) ' Light blue color
    color2 = RGB(255, 255, 255) ' White color
    startRow = 6
    startColumn = "A"
    endColumn = "O"

    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "Protection" And ws.Name <> "Help" Then
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            For i = startRow To lastRow
                If (i - startRow) Mod 2 = 0 Then
                    ws.Range(startColumn & i & ":" & endColumn & i).Interior.Color = color1
                Else
                    ws.Range(startColumn & i & ":" & endColumn & i).Interior.Color = color2
                End If
            Next i
        End If
    Next ws
End Sub
 
Upvote 0
And isn't it better to convert the data range to a table and choose the appropriate table formatting?
Using a fixed cell fill will cause a problem when sorting, and also when filtering.

Artik
 
Upvote 0
Welcome to the MrExcel board!

For the future, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block at the bottom of this post has more details. I have added the tags for you this time. 😊
 
Upvote 0

Forum statistics

Threads
1,226,113
Messages
6,189,046
Members
453,522
Latest member
Seeker2025

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top