Excel VBA inactive worksheet Range

Question

I want to format some cells of a worksheet, which is not active, via a function. This is my current code:

I call the function like that and give the worksheet with it:

i = DesignWorksheet(ws)

The function currently looks like that:

Function DesignWorksheet(ws As Worksheet)
 ws.Range(Cells(rowCurrent, 2), Cells(rowCurrent, 4)).Borders(xlEdgeBottom).Weight = xlThick
End Function

This leads to the error message:

Run-time error '1004': Method 'Range' of object '_Worksheet' failed

Now i tried to cut out the keyword 'Range' and it works. But now I only can format one cell at once:

Function DesignWorksheet(ws As Worksheet)
 ws.Cells(rowCurrent, 2).Borders(xlEdgeBottom).Weight = xlThick
End Function

Why does it work with 'ws.Cells()' but not with 'ws.Range(Cells(),Cells())'? Both variants work, if the worksheet is active, but only variant 2 works if another worksheet is active

Matt Cremeens Matt Cremeens · Accepted Answer · 2018-07-20T13:54:39

I believe you need the ws reference to each cell as well

Function DesignWorksheet(ws As Worksheet)
    ws.Range(ws.Cells(rowCurrent, 2), ws.Cells(rowCurrent, 4)).Borders(xlEdgeBottom).Weight = xlThick
End Function

Excel VBA inactive worksheet Range

1 Answers