range error with worksheet object

[cahinton]

I'm having a problem with a sub for shading a row. I started by recording a macro, then generalizing it as below:

Sub ShadeRow(aSheet As Worksheet, row As Long, nCols As Integer, blueFlag As Boolean)​

'​

' Shade iRow X cols blue or green​

'​

With aSheet.Range(Cells(row, 1), Cells(row, nCols)).Interior​

.Pattern = xlSolid​

.PatternColorIndex = xlAutomatic​

If (blueFlag) Then​

.ThemeColor = xlThemeColorAccent1​

Else​

.ThemeColor = xlThemeColorAccent6​

End If​

.TintAndShade = 0.799981688894314​

.PatternTintAndShade = 0​

End With​

End Sub
​

vba gives a runtime error 1004 indicating the range method failed. The calling routine gets the worksheet object by Worksheets(strSheetName), so I'm a little stumped. The other arguments are also correct and it makes no difference if I declare the first parameter as ByRef.
thanks in advance!

 

[pvr928]

Hi cahinton

Just to be sure - you know you've created a Sub that is called from another routine, ie this Sub takes parameters/inputs from another routine, and then uses those inputs to then run this sub?

I've run your ShadeRow() sub using the following code:

Sub CallSub()
Call ShadeRow(Sheets("Sheet1"), 2, 3, False)
End Sub

and

Sub CallSub()
Call ShadeRow(Sheets("Sheet1"), 2, 3, True)
End Sub

and both times your ShadeRow() has worked.

Consequently, the issue will be in the parameters being passed to ShadeRow(). I would start by looking at the

aSheet as Worksheet

parameter. Confirm what it is by using

Debug.Print aSheet.Name

just before you call ShadeRow() in the 'calling' Sub.

Let me know how you get by.

Cheers

pvr928

 

[Norie]

Try this.

Sub ShadeRow(aSheet As Worksheet, row As Long, nCols As Integer, blueFlag As Boolean)
'
' Shade iRow X cols blue or green
    With aSheet
        With .Range(Cells(row, 1), Cells(row, nCols)).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            If (blueFlag) Then
                .ThemeColor = xlThemeColorAccent1
            Else
                .ThemeColor = xlThemeColorAccent6
            End If
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
    End With

End Sub