0

I'm trying to set variable endofSheet to the range from cell C7 to the last filled cell in columnn C, within a For loop that cycles through each worksheet in the workbook. endofSheet will then be used as a range for a For loop that will match each row's interior color to that of its cell in the C column.

Sub prepReport()

Dim ws As Worksheet
Dim cell, endofSheet As Range

For Each ws In Worksheets
    
    ws.Name = ws.Range("E2").Value
    
    endofSheet = ws.Range("C7", ws.Range("C7").End(xlUp)) 'This is the problem line
    
    For Each cell In endofSheet
        cell.EntireRow.Interior.Color = cell.Interior.Color
    Next
    
Next


End Sub

It's been years since I've done real work in VBA, but I'm really wracking my brain here trying to figure out what I'm doing wrong.

I've resorted to reducing my code to the bare minimum to try and weed out any unforeseen conflicts. Any help with this would be greatly appreciated.

Brian M Stafford
  • 8,483
  • 2
  • 16
  • 25
akaorenji
  • 1
  • You forgot the leading `Set`: `Set endofSheet = ...`. Btw, if you want `cell` to be of type `Range`, you need to specify that: `Dim cell As Range, endofSheet As Range`. – BigBen Feb 14 '23 at 15:27
  • @BigBen Thanks, I definitely needed that. But now I'm getting "Run-time error '1004': Method 'Range' of object '_Worksheet' failed." Weird! – akaorenji Feb 14 '23 at 15:51
  • Btw, see [this](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba) for how to find the last used cell correctly. – BigBen Feb 14 '23 at 16:01

0 Answers0