Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Change cell color for every other row

Posted on 2011-09-08
16
Medium Priority
?
334 Views
Last Modified: 2012-05-12
I want to change the backcolor of every other row beginning in row 12. But I also want to be able to put a backcolor in cell M4 and then run a macro to change the backcolor of every other row based on that color in cell M4. How can this be done?
0
Comment
Question by:Lawrence Salvucci
  • 8
  • 4
  • 2
  • +1
16 Comments
 
LVL 4

Expert Comment

by:grogman
ID: 36507564
Here is a simple example, that will do every other row starting with Row 12 all the way to the end of the worksheet (the entire row):

Sub Test()
    Dim iColor As Long
    Dim iRow As Long
    Application.ScreenUpdating = False
    iColor = Sheet1.Range("M4").Interior.Color
    iRow = 12
    Do While iRow <= Sheet1.Rows.Count
        Sheet1.Rows(iRow).Interior.Color = iColor
        iRow = iRow + 2
    Loop
    Application.ScreenUpdating = True
End Sub

This code can take a little time to run. You can speed it up by narrowing down the range you need to work with. For example, instead of doing ALL rows down to the bottom of the sheet, you could just do rows 12 through 150 by changing the 'Do While' line to "Do While iRow <= 150". Additionally, instead of doing the color of the entire row, you could specify the columns (maybe columns A to L) by changing the next line to something like "Range(Sheet1.Cells(iRow, 1).Address & ":" & Sheet1.Cells(iRow, 12).Address).Interior.Color = iColor".
0
 
LVL 4

Expert Comment

by:grogman
ID: 36507567
Complete code of my second example:

Sub Test()
    Dim iColor As Long
    Dim iRow As Long
    Application.ScreenUpdating = False
    iColor = Sheet1.Range("M4").Interior.Color
    iRow = 12
    Do While iRow <= 150
        Range(Sheet1.Cells(iRow, 1).Address & ":" & Sheet1.Cells(iRow, 12).Address).Interior.Color = iColor
        iRow = iRow + 2
    Loop
    Application.ScreenUpdating = True
End Sub
0
 
LVL 50
ID: 36507577
Hello,

here is an alternative requiring no code, but using conditional formatting instead. Enter a number into cell M4, either 1, 2 or 3. The cell will change color due to the conditional format set on the cell.

Rows 12 and following have been formatted with three rules along the lines of

=AND(MOD(ROW(),2),$M$4=1)

This is in Excel 2003, limited to three conditional formats. In later versions, you can have more.

See attached

cheers, teylyn
Book1.xls
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 19

Expert Comment

by:Arno Koster
ID: 36508973
Instead of looping through all rows, you could also opt for only coloring the rows up to the last row that contains data :

the first option only colors the columns in a box enclosing the data in the worksheet, the latter option colors the complete rows.

Sub change_cell_colors()

    For Each Row In UsedRange.Rows
        If IsOdd(Row.Row) Then
            '-- only color the 'used range'
            Row.Interior.Color = vbCyan
            '-- color complete rows
            Rows(Row.Row).Interior.Color = vbCyan
        Else
            Rows(Row.Row).Interior.Color = xlNone
        End If
    Next Row

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 36509433
@grogman - I like your approach but would it be possible to modify your code to only highlight the rows down to where if finds the last row of data?
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36509481
@lsalvucci,

have you tried my code ?
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 36509739
@akoster - I have tried your code but I would like to be able to change the color based on the color in cell M4.
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36509848
Then you should combine the macro's from progman and me :
Sub change_cell_colors()

    For Each Row In UsedRange.Rows
        If IsOdd(Row.Row) Then
            '-- color complete rows
            Rows(Row.Row).Interior.Color = Range("M4").Interior.Color
        Else
            Rows(Row.Row).Interior.Color = xlNone
        End If
    Next Row

End Sub

Open in new window

0
 
LVL 50
ID: 36510009
@akoster,

I assume you have tested your suggestion, but neither XL 2003 nor XL 2010 run that macro without throwing multiple errors when I try the code in a standard module.

I cannot get your code to run error free in 2010 or 2003. Does it run in your environment? If so, what version is that? Curious to learn about the differences in versions.

IsOdd is not a VBA function. In XL 2003 it does not exist at all. In XL 2010 it has to be quoted with

Application.Worksheetfunction.IsOdd(etc....

After correcting that, the next error is thrown for

For Each Row In UsedRange.Rows

"Object required"  -- If the code is run from a Standard module, the sheet needs to be qualified.

In any case, after getting it to run without errors from a Sheet module, the code overwrites the color in M4 with xlNone and then does not apply any color to the following rows.

Did you test that code at all? Can you post a file where it works?
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36510547
Teylun,

isOdd indeed is not a regular excel formula, I should have posted the code for that as well :

Function isOdd(value) As Boolean
    isOdd = (value Mod 2 = 1)
End Function

Open in new window


I am currently working in Excel 2007 but these codes are so generic it should work in 2003 and 2010 as well.

Also, when you run the code in a standard module it would lead to a number of errors. This is because I did not make a reference to a particular worksheet in which the range and usedrange should be found. Thus the code would either have to be placed in a worksheet code section instead of in a standard module, or a reference to the particular wroksheet would have to be added.

eg:
Sub change_cell_colors()

    For Each Row In Worksheets("Sheet1").UsedRange.Rows
        If IsOdd(Row.Row) Then
            '-- color complete rows
            Worksheets("Sheet1").Rows(Row.Row).Interior.Color = Worksheets("Sheet1").Range("M4").Interior.Color
        Else
            Worksheets("Sheet1").Rows(Row.Row).Interior.Color = xlNone
        End If
    Next Row

End Sub

Open in new window


Finally, please note that it might be advisable to exchange the coloring to the even rows instead of the odd rows because otherwise range M4 will be made colorless.


0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36510567
an example :  test-changing-colors.xlsm
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 36512022
@akoster - How do I have this start at Row 12?
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36521498
When you want to start at row 12 and work down to the last used row, you could do
Sub change_cell_colors()

    For Row = 12 To Worksheets("Sheet1").UsedRange.Rows.Count Step 2
        Worksheets("Sheet1").Rows(Row).Interior.Color = Worksheets("Sheet1").Range("M4").Interior.Color
    Next Row

End Sub

Open in new window


the drawback is that when you move rows up & down, the coloring will only be added and not removed where not appropriate.
When this is an issue, you could use

Sub change_cell_colors()

    For Row = 12 To Worksheets("Sheet1").UsedRange.Rows.Count Step 2
        If (Row Mod 2 = 1) Then
            Worksheets("Sheet1").Rows(Row).Interior.Color = Worksheets("Sheet1").Range("M4").Interior.Color
        Else
            Worksheets("Sheet1").Rows(Row).Interior.Color = xlNone
        End If
    Next Row
    
End Sub

Open in new window

0
 
LVL 19

Accepted Solution

by:
Arno Koster earned 2000 total points
ID: 36521501
the last option should have been
Sub change_cell_colors()

    For Row = 12 To Worksheets("Sheet1").UsedRange.Rows.Count
        If (Row Mod 2 = 1) Then
            Worksheets("Sheet1").Rows(Row).Interior.Color = Worksheets("Sheet1").Range("M4").Interior.Color
        Else
            Worksheets("Sheet1").Rows(Row).Interior.Color = xlNone
        End If
    Next Row
    
End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 36522158
Thank you for all your help.  I greatly appreciate it.
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36522163
you're welcome !
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question