« Shopn2000.com | Main | Shorting the S&P »

October 02, 2002

Alternating Shading Excel Macro

This is an excel macro which will help you alternate colors on a spreadsheet. What you do is color a range of cells whatever color you want. Then, run this macro. The macro will color every other row white. It only effects the area that you have currently selected. Enjoy.

Sub AlternateWhiteBackground()
Dim start, r As Long
start = 0

  If Not Selection Is Nothing Then
    For Each rw In Selection.Rows
    If start = 0 Then
        start = rw.Row
    End If
      If (rw.Row - start) Mod 2 <> 0 Then
        rw.Interior.ColorIndex = 2
      End If
    Next rw
  End If

End Sub

Posted by Chris at October 2, 2002 07:26 PM

Subscribe to this entry:   Email address:   

Trackback Pings

TrackBack URL for this entry:
http://www.christulino.com/cgi-bin/mt/mt-tb.cgi/34

Comments

This is great!

bow I just need to combinde it with the "Custom Color" thingie to make 2 rows of neato colors.

Coding...

Posted by: emp at December 18, 2003 11:57 AM

Very cool! Thanks for the code man!


N8

Posted by: Nate at December 29, 2003 08:01 PM

this one is what im looking for! this saved my time!

Posted by: jp at February 4, 2004 10:00 PM

Chris,

How would you do this with alternating columns?

Posted by: Carla at March 31, 2004 10:00 AM

If you want to do this with columns:

Sub AlternateWhiteBackground()
Dim start, r As Long
start = 0

If Not Selection Is Nothing Then
For Each col In Selection.Columns
If start = 0 Then
start = col.Column
End If
If (col.Column - start) Mod 2 <> 0 Then
col.Interior.ColorIndex = 2
End If
Next col
End If

I didn't test it, but that's the gist.
End Sub

Posted by: Chris at March 31, 2004 07:50 PM

This is just a little more on Chris' macro, this one starts with pastel purple, and alternates to white then purple. I'm working on one that updates as you add a new row

Dim start, r As Long
start = 0

If Not Selection Is Nothing Then
For Each rw In Selection.Rows
If start = 0 Then
start = rw.Row
End If
If (rw.Row - start) Mod 1 <> 2 Then
rw.Interior.ColorIndex = 2
End If
If (rw.Row - start) Mod 2 <> 0 Then
rw.Interior.ColorIndex = 39
End If

Next rw
End If

Posted by: Jason Davis at August 25, 2004 01:09 PM