Solved

Change cell color for every other row

Posted on 2011-09-08
16
314 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
Comment Utility
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
Comment Utility
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

Expert Comment

by:teylyn
Comment Utility
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
 
LVL 19

Expert Comment

by:akoster
Comment Utility
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
Comment Utility
@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:akoster
Comment Utility
@lsalvucci,

have you tried my code ?
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
@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:akoster
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 50

Expert Comment

by:teylyn
Comment Utility
@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:akoster
Comment Utility
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:akoster
Comment Utility
an example :  test-changing-colors.xlsm
0
 
LVL 1

Author Comment

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

Expert Comment

by:akoster
Comment Utility
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:
akoster earned 500 total points
Comment Utility
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
Comment Utility
Thank you for all your help.  I greatly appreciate it.
0
 
LVL 19

Expert Comment

by:akoster
Comment Utility
you're welcome !
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now