Color Cells based on Loop

[cgsamaras]

What would be the best way to go about coloring every 17 rows (rows 4:416 - columns C:V), skipping a row in between each 16 rows. Need the background color to be blue. So basically - every 17R x 20C should be blue skipping 1 x row in between each set of 17 rows from row 4 to row 416.

Appreciate any help you can provide.

Thanks,
Chris

 

[texasalynn]

Welcome to MrExcel board...

Are you wanting a VBA solution? And just to clarify you want to color 17 rows, leave one uncolored, then color the next 17 and so on through the designated range?

 

[cgsamaras]

Yes. I am looking to do this in VBA and the only way I have found to get it working is making a macro that goes through each range as so -

Range("C4:V20").Select
Selection.Interior.ColorIndex = 37
Selection.Font.ColorIndex = 49
Range("C22:V38").Select
Selection.Interior.ColorIndex = 37
Selection.Font.ColorIndex = 49

and so on and so on....

I know there has to be a much better way.

Any help would be awesome!

Thanks

 

[GTO]

Please note that the worksheet is not qualified, so this will run against the active sheet.

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> exa()<br><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = 4 <SPAN style="color:#00007F">To</SPAN> 400 <SPAN style="color:#00007F">Step</SPAN> 18<br>        <SPAN style="color:#00007F">With</SPAN> Range(Cells(i, "C"), Cells(i + 16, "V"))<br>            .Interior.ColorIndex = 37<br>            .Font.ColorIndex = 49<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Mark