Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: EASY - Formatting Odd Numbered Rows

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm looking for the code to shade the interior of each odd numbered row to colorindex=15. Right now I go line by line with a manual counter that changes from 0 to 1 or 1 to 0 at the end of each run of the loop. There's got to be a more efficient way to do this. Any suggestions?

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    (1) Click on Format > Conditional Formatting.
    (2) In the first drop down box, select "Formula Is"
    (3) In the field at the right of that, enter
    =MOD(ROW(),2)=1
    (4) Click the Format button, then Patterns tab.
    (5) Color index 15 is light gray, so choose that and hit OK, OK.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sub colorevenrows()

    Do While Not IsEmpty(ActiveCell)
    If ActiveCell.Row Mod 2 = 0 Then
    ' for odd
    ' if activecell.row mod 2 <> 0 then
    ActiveCell.EntireRow.Interior.ColorIndex = 15
    End If
    ActiveCell.Offset(1, 0).Select
    Loop

    End Sub

    ni****h desai
    http://www.pexcel.com

    [ This Message was edited by: nisht on 2002-04-18 03:33 ]

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    jrnyman --

    Thinking about your question and my response, you asked for code but I gave you a non-code answer because you also asked for efficiency. Loops tend to be inefficient, but so might a large range that is conditionally formatted. There're pros and cons with either approach; it depends on your preference and spreadsheet design.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks everyone for the help.

    For anyone who thinks VBA is just like English chew on this:

    Do While Not IsEmpty(ActiveCell)

    translates to ENGLISH as..

    Do (below) while active cell is not empty

    Must have been too easy to do it that way....

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •