Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every 'nth' row?
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 ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every 'nth' row?
Jack,
Could I suggest conditional formatting? Select all the rows to apply to, including the in -between non-coloured rows. Goto menu FormatConditional 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) "jack27" wrote in 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? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every 'nth' row?
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 On Sat, 6 Dec 2003 18:49:58 -0000, "Bob Phillips" wrote: Jack, Could I suggest conditional formatting? Select all the rows to apply to, including the in -between non-coloured rows. Goto menu FormatConditional 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every 'nth' row?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every 'nth' row?
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? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every 'nth' row?
"Pikus" wrote in message
... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every 'nth' row?
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 -----Original Message----- "Pikus" wrote in message ... 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. . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every 'nth' row?
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) "Pikus" wrote in message ... 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 -----Original Message----- "Pikus" wrote in message ... 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. . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Color every 'nth' row?
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? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't format cell color/text color in Office Excel 2003 in fil | Excel Discussion (Misc queries) | |||
Finding the date on the 'nth' Monday in this Month in this Year | Excel Worksheet Functions | |||
Can't format cell color/text color in Office Excel 2003 in files . | Excel Discussion (Misc queries) | |||
My fill color and font color do not work in Excel Std Edition 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 will not display color fonts or color fill cells | Excel Worksheet Functions |