change macro that changes interior color

[still learning]

Hi
A few years ago, I made this macro with the help of this forum.
What it does is fill every other row interior from A to I with a blue shade
It starts at A3 , then stops at the last cell in A that has data.
Then it moves the cursor to the last cell in A and moves down one.

Sub filleveryotherrow ()
‘ change interior color
Dim i As Long
  For i = 3 To Columns("A:I").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row Step 2
    With Rows(i).Resize(, 9).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 16772049
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
  Next i
lastrow
End Sub

Sub lastrow()
' find last row
x = Range("A" & Rows.Count).End(xlUp).row
Range("A" & x + 1).Select
    Selection.End(xlUp).Select
ActiveWindow.ScrollRow = ActiveCell.row - 12
ActiveCell.Offset(1, 0).Range("A1").Select   
End Sub

What I don’t know how to do is add the following at the beginning.

  For i = 3 To Columns("A:I").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row Step 2
    With Rows(i).Resize(, 9).Interior
‘ this will un color each line
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0 
 End With
  Next i

What I want to do is first change the interior of all the rows to “no color”, then make every other row light blue
What happens is when I add data and then use sort, the row colors “get at of wack”
This will un-color ever row with data and then make every other row light blue.
What I do now is highlight it all and change the interior to no color
Then I run the fill…macro

mike

 

[HighAndWilder]

Hi
A few years ago, I made this macro with the help of this forum.
What it does is fill every other row interior from A to I with a blue shade
It starts at A3 , then stops at the last cell in A that has data.
Then it moves the cursor to the last cell in A and moves down one.

Sub filleveryotherrow ()
‘ change interior color
Dim i As Long
  For i = 3 To Columns("A:I").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row Step 2
    With Rows(i).Resize(, 9).Interior
      .Pattern = xlSolid
      .PatternColorIndex = xlAutomatic
      .Color = 16772049
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
  Next i
lastrow
End Sub

Sub lastrow()
' find last row
x = Range("A" & Rows.Count).End(xlUp).row
Range("A" & x + 1).Select
    Selection.End(xlUp).Select
ActiveWindow.ScrollRow = ActiveCell.row - 12
ActiveCell.Offset(1, 0).Range("A1").Select  
End Sub

What I don’t know how to do is add the following at the beginning.

  For i = 3 To Columns("A:I").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row Step 2
    With Rows(i).Resize(, 9).Interior
‘ this will un color each line
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
 End With
  Next i

What I want to do is first change the interior of all the rows to “no color”, then make every other row light blue
What happens is when I add data and then use sort, the row colors “get at of wack”
This will un-color ever row with data and then make every other row light blue.
What I do now is highlight it all and change the interior to no color
Then I run the fill…macro

mike

You could use a table and then alternate rows will be coloured or use a Conditional Formatting formula : =MOD(ROW(),2)=0

 

[still learning]

Hi HighandwWilder
I'll look into that
If I can't use it, I'll make up a sample sheet and post it
thanks for the reply

mike