Change Pattern on Selected Rows Inside a Named Range

[Eldrod]

I have a particular problem with some code I'm hoping someone more familiar with Range and/or Offsets can help me with. I have a multi-sheet workbook with named ranges defined on most of the sheets. What I want to do is search the first column of each named range and if a matching code is read, then reset the .Interior.Pattern to xlPatternNone. This snippet of code works fine as long as I'm on the sheet that contains the Named Range. However, the code I want to work needs to scan all the workbook's named ranges to reset them all. I don't want to do a select on each sheet as there should be a better way. Here's the code I have issues with

Sheets(Tab_Name).Range(Cells(xloop, 1), Cells(xloop, ColumnstoProcess)).Interior.Pattern = xlPatternNone

and presently this generates a 1004 Application-defined error when it hits the code to reset the pattern.

For Each Name_Entry In ListofNames
    If InStr(1, Name_Entry.Value, "#REF") = 0 Then
        Tab_Name = Replace(Mid(Name_Entry.Value, 2, InStr(2, Name_Entry.Value, "!") - 2), "'", "")
        Name_Entry_Range = Mid(Name_Entry.Value, InStr(1, Name_Entry.Value, "!") + 1)
            If InStr(1, Name_Entry.Name, "_Print_Area") > 0 Then
                RowstoProcess = Sheets(Tab_Name).Range(Name_Entry).Rows.Count
                ' determine the number of colunms in the named range
                ColumnstoProcess = Sheets(Tab_Name).Range(Name_Entry).Columns.Count
                If RowstoProcess > 0 Then
                    With Sheets(Tab_Name).Range(Name_Entry_Range)
                        For xloop = 1 To RowstoProcess
                            Tag_Type = ""
                            If InStr(1, LCase(.Cells(xloop, 1).Formula), "hide_row:") > 0 Then
                                If InStr(1, LCase(.Cells(xloop, 1).Formula), "true") > 0 Then Tag_Type = "True"
                            End If
                            If InStr(1, LCase(.Cells(xloop, 1).Value), "hide_row:") > 0 Then
                                If InStr(1, LCase(.Cells(xloop, 1).Value), "true") > 0 Then Tag_Type = "True"
                            End If
                                                   
                            If Tag_Type = "True" Then 
                                        ' this section will restore the cells in the row, but since restoring wipes out the background
                                        ' we need to save it so we can restore it as well
                                        ReDim Cells_BG(ColumnstoProcess)
                                        For iloop = 1 To ColumnstoProcess
                                            Cells_BG(iloop) = .Cells(xloop, iloop).Interior.ColorIndex
                                        Next iloop
Sheets(Tab_Name).Range(Cells(xloop, 1), Cells(xloop, ColumnstoProcess)).Interior.Pattern = xlPatternNone
                                        ' restore the background colors we saved
                                        For iloop = 1 To ColumnstoProcess
                                            If Cells_BG(iloop) = xlColorIndexAutomatic Then
                                                .Cells(xloop, iloop).Interior.ColorIndex = xlNone
                                            Else
                                                .Cells(xloop, iloop).Interior.ColorIndex = Cells_BG(iloop)
                                        End If
                                        Next iloop
                            End If
                        Next xloop
                    End With
                End If
            End If
    End If  'End of Handling Print Area code
Next Name_Entry

The object is to locate and reset only the row of cells that are contained in the named range. Any help would be greatly appreciated!

 

[Derek Brown]

If you don't want to activate each worksheet, you will probably have to analyse the 'RefersTo' property of the range and then use worksheet CELL to reference each cell in turn.

 

[Eldrod]

Thanks, Derek. I didn't come across any better way so since my code was already looping through the cells in the row to store the background color, I just set the cell.interior.pattern at the same time. The challenge I was trying to resolve was to set a pattern on cells then remove it without changing the cell's background color. Apparently, just removing the pattern resets the background color to no fill.