A simple macro to make every 3rd row a specific background color???

L

LordJezo

How would I make a simple macro script that would make every third row
color of my choosing, as in rows 3, 6, 9, etc.

Is there an easy way to do such a simple task
 
K

Ken Wright

You don't need a macro, and if you used one you would lose the integrity of the
colouring anytime you sorted the data, or deleted cells etc.

Use a conditional format to do this and it will not move when you sort the
rows:-

Do CTRL+A, then Format / Conditional Formatting / Change 'cell value is' to
'formula is' and put
in =MOD(ROW(),2)=1

Now click on the format button and choose a nice pastel colour from the patterns
tab. Hit OK till
you are out and you are done.

You could also put in
=MOD(ROW(),2)=0 if you wanted the other rows to be coloured.

You could also put in
=MOD(ROW(),3)=0 if you wanted every 3rd row to be coloured.

You could also put in
=MOD(ROW(),4)=0 if you wanted every 4th row to be coloured.

You could also put in
=MOD(ROW(),5)=0 if you wanted every 5th row to be coloured.

But you probably get the drift by now. Flipping the 1 and the 0 will determine
whether or not you
start with a coloured or a non-coloured row.


If you want to create alternate green bars made up of say 3 rows (or any other
color), you can
also use:

For every three rows:
=MOD(ROW()-1,6)<3

For every four rows:
=MOD(ROW()-1,8)<4

For other number of shadings, just make the 2nd number (6 or 8 in example) twice
as much as the
3rd number (3 or 4 in example)
 
P

popgroove

but if you must have a macro, try this

Public Sub EveryThird()
For X = 3 To 1000 '1000 is how far down the sheet to go
Rows(X).Select

With Selection.Interior
.ColorIndex = 42
.Pattern = xlSolid
End With
X = X + 2

Next X

End Sub



Mark Wielgus
www.AutomateExcel.co
 
R

Rutgers_Excels

You can also try this macro...

Sub RowColor()

Dim StartRow
Dim EndRow

StartRow = InputBox(prompt:="From which row would you like_ to start?"
Title:="Start Row")
EndRow = InputBox(prompt:="To which row would you like to_ end?"
Title:="End Row")
For irow = StartRow To EndRow Step 3
Rows(irow).Select
Selection.Interior.ColorIndex = 36
Next
End Sub

This will allow you to set the start and end rows in an input box.
This way you don't have to color every single row in your sheet
 
D

David

Rutgers_Excels < wrote
This way you don't have to color every single row in your sheet.
Click to expand...

You can also select a range and use the =MOD(ROW(),x)=x method to limit
shading to a range of rows & columns
 
Top