Color every 'nth' row?

[jack27]

Does anyone know how i can create a macro that will allow me to colo
every 'nth' row? I'm thinking a box would pop up asking which row t
color, ie if i wanted to color every fourth row i'd enter 4, if iwante
to color every 17th row i'd enter 17 etc... and on clicking OK th
backgroud color of the respective rows would change color?

Any ideas

 

[Bob Phillips]

Jack,

Could I suggest conditional formatting?

Select all the rows to apply to, including the in -between non-coloured
rows.
Goto menu Format>Conditional Formatting
Change Condition 1 to FoprmulaIs
Input a formula of =MOD(ROW(),$A$1)=$B$1
Click the Format button
Select the pattern tab
Choose a colour
OK
OK

A1 holds the set size (your 4,17), and B1 is the row within that set to
colour. So A1=4, B1=2 will colour row, 6, etc. A1=3, B1 =1 colours 1,4,7.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Does anyone know how i can create a macro that will allow me to color
every 'nth' row? I'm thinking a box would pop up asking which row to
color, ie if i wanted to color every fourth row i'd enter 4, if iwanted
to color every 17th row i'd enter 17 etc... and on clicking OK the
backgroud color of the respective rows would change color?

Any ideas?


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements

 

[Rick]

Sub Color_Rows()
Dim RowNum As Integer
RowNum = InputBox("Ever what row would you like
colored ???")
For I = 0 To 100 Step RowNum
With ActiveSheet
..Range("A1:IV1").Offset(I, 0).Interior.ColorIndex = 8
End With
Next I
End Sub

 

[Gord Dibben]

Jack

If you want a macro............

Sub ColorEveryNthCell()
Dim rng As Range, i As Integer
Dim srng As Range
With ActiveSheet
On Error Goto EndAll
startrow = InputBox("Which Row to Start")
Set rng = Cells(startrow, 1)
everywhich = InputBox("How far apart")
numbrows = InputBox("How many times")
For i = 1 To numbrows - 1
Set prng = Union(rng, rng.Offset(everywhich * i, 0))
prng.EntireRow.Interior.ColorIndex = 3 ''Red
Next
End With
EndAll:
End Sub

Gord Dibben XL2002

 

[Pikus]

Here's the short answer:

nRows = InputBox("Highlight every n rows")

For x = 1 To 100
If x Mod nRows = 1 Then <<Or exchange the number 1
With Rows(x).Interior
.ColorIndex = 6
End With
End If
Next x

You can change a few things to perfect this.

-For x = 1 To 100
Change "100" to whatever you want to be the last possible
highlighted Row Or "nRows * 20" for instance if you want
20 rows colored.
Either way you must give some limit to the number of
hilighted rows or you'll get an error.

-If x Mod nRows = 1 Then
Leaving this line as it is always hilights the first row
and highlights every x rows after that. If you want to
start hilighting with the row whose number you enter (i.e.
when you enter "4" it highlights the 4th row and every 4th
row thereafter) you chould change the number 1 to 0.

-.ColorIndex = 6
The number 6 here means yellow. Here are the meanings of
the numbers 1 - 5:
1 = Black
2 = White
3 = Red
4 = Green
5 = Blue
The numbers 1 through 56 will work here. You can use this
to learn their corresponding colors:

For x = 1 To 56
Rows(x).Interior.ColorIndex = x
Next x

-----Original Message-----

Does anyone know how i can create a macro that will allow me to color
every 'nth' row? I'm thinking a box would pop up asking which row to
color, ie if i wanted to color every fourth row i'd enter 4, if iwanted
to color every 17th row i'd enter 17 etc... and on clicking OK the
backgroud color of the respective rows would change color?

Any ideas?


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step

guide to creating financial statements

 

[Pikus]

I like my solution best...

-----Original Message-----

Does anyone know how i can create a macro that will allow me to color
every 'nth' row? I'm thinking a box would pop up asking which row to
color, ie if i wanted to color every fourth row i'd enter 4, if iwanted
to color every 17th row i'd enter 17 etc... and on clicking OK the
backgroud color of the respective rows would change color?

Any ideas?


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step

guide to creating financial statements

 

[Bob Phillips]

The numbers 1 through 56 will work here. You can use this
to learn their corresponding colors:

Or you could simply look at Colorindex Property in VBA Help.

 

[Pikus]

Thanks for the pointer! I just started learning to
program and the book I read didn't get into much detail.
I don't guess you have any recommended reading that might
pick up where the book left off huh? - Pikus

 

[Bob Phillips]

I don't know what book you read, and even though my suggestion doesn't
include the fact that the ColorIndex Property shows the list in VBA Help,
the book I always recommend is John Green, Rob Bovey, Stephen Bullen et al's
Excel 2002 VBA Programmers Reference Manual, published by Wrox, ISBN
0-7645-4371-7.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)