Use Excel VBA macro color rows that have an even number in column B

I wan to use a macro in excel to color all rows (or a range in a row) that have an even number in column B.
The script below does what I want except that it only colors a single cell, not the whole row (or a range in a row).

Sub AlternateRowColors()
Dim lastRow As Long

lastRow = Range("A1").End(xlDown).Row

For Each Cell In Range("A1:A" & lastRow) ''change range accordingly
    If Cell.Value Mod 2 = 1 Then ''highlights row 2,4,6 etc|= 0 highlights 1,3,5
       Cell.Interior.ColorIndex = 15 ''color to preference
    Else
        Cell.Interior.ColorIndex = xlNone ''color to preference or remove
    End If
Next Cell

End Sub
Matt JohnsonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Your description doesn't match with the code you have provided though you claim that it does what you want to achieve.

Please try this...

The following code will highlight all the cells in column B if they contain an even number in them else it will remove the interior color from them.
Sub HighlightCellsWithEvenNumbers()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To lr
   If IsNumeric(Cells(i, "B")) Then
      If Cells(i, "B") Mod 2 = 0 Then
         Cells(i, "B").Interior.ColorIndex = 15
      Else
         Cells(i, "B").Interior.ColorIndex = xlNone
      End If
   End If
Next i
End Sub

Open in new window

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Sub AlternateRowColors()
'151115 modified by strive4peace
   Dim lastRow As Long _
      , nLastCol As Long _
      , nRow As Long _
      , iColor As Integer
   Dim rng As Range
   
   lastRow = Range("A1").End(xlDown).Row
   nLastCol = ActiveSheet.UsedRange.Columns.Count

   For Each rng In Range("b1:b" & lastRow) ''change range accordingly
      nRow = rng.Row
      iColor = xlNone ''color to preference or remove
      If IsNumeric(rng.Value) Then
         If rng.Value Mod 2 = 1 Then ''highlights row 2,4,6 etc|= 0 highlights 1,3,5
            iColor = 15 ''color to preference
         End If
      End If
      Range(Cells(nRow, 1), Cells(nRow, nLastCol)).Interior.ColorIndex = iColor

   Next rng
   
   Set rng = Nothing

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Matt JohnsonAuthor Commented:
If the cell in column B is an even number, then i want to color the whole row (or at least a range in the row).  For example, if cell B7 = 6 (an even number), then I want to color the range A7:Z7, not just the cell with the even number (B7).
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Matt,

I modified your code to do that ;) ~
Matt JohnsonAuthor Commented:
With preliminary testing, it looks like crystal has the solution.  I will test further tomorrow and award points if it works.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
an alternate way to get the last column is:
WITH activesheet
   nLastCol = .Cells(1, .Columns.Count).End(-4159).Column   ' xlToLeft = -4159
end with

Open in new window

assuming that row 1 will be the determining row

using WITH is better performance since 2 references are made to the sheet.  When a property or method starts with . (dot), whatever is in the WITH statement is the object it will act upon

you can use xlToLeft instead of -4159, but I usually get the value of the constant so I can run code from Access and use late-binding ~

in the example, I used ActiveSheet.UsedRange.Columns.Count but that might pick up extra columns if you used Excel as a scratchpad ;)
Matt JohnsonAuthor Commented:
Thanks.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Matt ~ happy to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.