vbs script for Excel to color rows

I was wonder if someone would mind writting me a small script to help me
out. I have a HUGE spreadsheet that has one colum which is numbered
sequentially, but any number may be repeated 2 or mor times. There are
many other colums which contain data, so this will help with readability.
Example:
1
1
1
2
2
3
3
3
3
4
4
5
5
5
5

I want to color each *row* (not the cell, but the whole row) of the same
number the same color. Here is my "program logic" in psudeo-code

Rowcolor = 1
Currentcontents = value of current cell
Nextcontents = value of current cell

Do while value of current cell is not blank
If nextcontents = currentcontents then
Set row color = rowcolor
Move down one cell
Nextcontents = value of current cell
Else
Rowcolor = rowcolor + 1
Currentcontents = nextcontents
End if
End Do

I'm sure this is very simple for those who know vbs - I just don't and
can't find anyother way to accomplish this. Doing it manually would
take FOREVER as the spreadsheet has over 30,000 rows.

Thanks very much to anyone will to help out!!!
Mike

Jim
Sat Oct 08 16:53:12 CDT 2005

Mike,

My Excel Add-in "Shade Data Rows" does that.
It will shade groups of like valued rows or will shade by every nth row.
Shades across selection or entire sheet.
Choice of shade colors and an option to skip hidden rows.
It is run from a custom menu item on the Format menu.

Comes with a one page Word.doc install/use file.
It is free upon email request. Remove XXX from my email address.

Regards,
Jim Cone
San Francisco, USA
jim.coneXXX@rcn.comXXX


"Mike Bailey" <mbailey@beaumontproducts.com> wrote in message news:OOaqgrEzFHA.1168@TK2MSFTNGP15.phx.gbl...
I was wonder if someone would mind writting me a small script to help me
out. I have a HUGE spreadsheet that has one colum which is numbered
sequentially, but any number may be repeated 2 or mor times. There are
many other colums which contain data, so this will help with readability.
Example:
1
1
1
2
2
3
3
3
3
4
4
5
5
5
5
I want to color each *row* (not the cell, but the whole row) of the same
number the same color. Here is my "program logic" in psudeo-code
Rowcolor = 1
Currentcontents = value of current cell
Nextcontents = value of current cell
Do while value of current cell is not blank
If nextcontents = currentcontents then
Set row color = rowcolor
Move down one cell
Nextcontents = value of current cell
Else
Rowcolor = rowcolor + 1
Currentcontents = nextcontents
End if
End Do
I'm sure this is very simple for those who know vbs - I just don't and
can't find anyother way to accomplish this. Doing it manually would
take FOREVER as the spreadsheet has over 30,000 rows.
Thanks very much to anyone will to help out!!!
Mike

Mike
Mon Oct 10 10:42:30 CDT 2005

Thank you VERY much! This is exactly what I needed - and has saved me
hours of work!

Mike Bailey




Jim Cone wrote:
> Mike,
>
> My Excel Add-in "Shade Data Rows" does that.
> It will shade groups of like valued rows or will shade by every nth row.
> Shades across selection or entire sheet.
> Choice of shade colors and an option to skip hidden rows.
> It is run from a custom menu item on the Format menu.
>
> Comes with a one page Word.doc install/use file.
> It is free upon email request. Remove XXX from my email address.
>
> Regards,
> Jim Cone
> San Francisco, USA
> jim.coneXXX@rcn.comXXX
>
>
> "Mike Bailey" <mbailey@beaumontproducts.com> wrote in message news:OOaqgrEzFHA.1168@TK2MSFTNGP15.phx.gbl...
> I was wonder if someone would mind writting me a small script to help me
> out. I have a HUGE spreadsheet that has one colum which is numbered
> sequentially, but any number may be repeated 2 or mor times. There are
> many other colums which contain data, so this will help with readability.
> Example:
> 1
> 1
> 1
> 2
> 2
> 3
> 3
> 3
> 3
> 4
> 4
> 5
> 5
> 5
> 5
> I want to color each *row* (not the cell, but the whole row) of the same
> number the same color. Here is my "program logic" in psudeo-code
> Rowcolor = 1
> Currentcontents = value of current cell
> Nextcontents = value of current cell
> Do while value of current cell is not blank
> If nextcontents = currentcontents then
> Set row color = rowcolor
> Move down one cell
> Nextcontents = value of current cell
> Else
> Rowcolor = rowcolor + 1
> Currentcontents = nextcontents
> End if
> End Do
> I'm sure this is very simple for those who know vbs - I just don't and
> can't find anyother way to accomplish this. Doing it manually would
> take FOREVER as the spreadsheet has over 30,000 rows.
> Thanks very much to anyone will to help out!!!
> Mike