excel vba subroutine call fails

Question

I have the following problem. I want to call a soubroutine for changing the background color for a cell range. The cell range is calculated with cells(1,1) and then the address is calculated to receive A1.

Before the subroutine is called I get the addresses for my cells like this:

Range1 = cells(4, 4).Address(RowAbsolute:=False, ColumnAbsolute:=False)
Range2 = cells(4, CellAmount - 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)

I thought I need this because the subroutine is declared like this:

Sub SetBGLightGrey(cells As String)

    range(cells).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15921906
        .TintAndShade = 0
       .PatternTintAndShade = 0
    End With

End Sub

Range 1 and Range 2 are strings and I concat it to a range declaration:

RangeArea = """" & Range1 & ":" & Range2 & """"

When I call my subroutine like this:

Call SetBGLightGrey(RangeArea)

I get the following error-message:

"Run-time error '1004': Method 'Range' of object '_Global' failed. I don't understand it because if I call the subroutine with the correct cell values:

Call SetBGLightGrey("D4:K4")

it works. It is string and of the same value. This simply cannot be can it?

GSerg · Accepted Answer · 2011-06-07 11:21:15Z

2

You do not need quotes around RangeArea.

RangeArea = Range1 & ":" & Range2

But then, why would you want to pass ranges around as strings and then convert them back to ranges? Pass the range objects all the time.

Sub SetBGLightGrey(byval cells as range)
  With cells.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 15921906
    .TintAndShade = 0
    .PatternTintAndShade = 0
  End With
End Sub 

SetBGLightGrey range(cells(4, 4), cells(4, CellAmount - 1))

answered Jun 7, 2011 at 11:21

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

excel vba subroutine call fails

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related