Using ShadeAlternate Subroutines as Macro

[dbfoxnh]

I located the exact tip I was looking for in ExcelTip for shadin
alternate rows. What I need to do is select the rows and columns
want shaded and then call a macro to do the job. I found the tip a
the following URL:

http://www.exceltip.com/st/Row_and_column_background_color_using_VBA_in_Microsoft_Excel/488.html

For me, the problem is that they are written as subroutines tha
require arguments to operate. Can someone help me, first time usin
VBA in Excel, to come up with a way to allow me to use the subroutin
to shade the rows in the area that I highlight?

Exceltip.com is a great resource. Not sure why I hadn't found i
before!

Thanks!

Davi

 

[Trevor Shuttleworth]

David

the first one could be modified thus:

Sub ShadeAlternateRowsModified()
Dim rngTarget As Range, intColor As Integer, lngStep As Long
' adds a background color = intColor to every lngStep rows in rngTarget
' example: ShadeAlternateRows Range("A150"), 27, 2
' colors every 2 rows light yellow
Dim r As Long
Set rngTarget = Selection
intColor = 27
lngStep = 2
If rngTarget Is Nothing Then Exit Sub
With rngTarget
.Interior.ColorIndex = xlColorIndexNone
' remove any previous shading
For r = lngStep To .Rows.Count Step lngStep
.Rows(r).Interior.ColorIndex = intColor
Next r
End With
End Sub

I'll leave the other to you.

Regards

Trevor