I have a piece of code that is trying to overwrite the value in a named range, but I am getting <Object doesn’t support this property or method>

Is it possible to name a range without it being slaved to a single sheet? Like if I select “rnge1” on sheet 1, it will select range e2:e7 on sheet 1, and then I can go to sheet 2 and select “rnge1” and it will select range e2:e7 on sheet 2.

In my Excel workbook, I’ve made a single cell a named range. Instead of using vba to do this, I just went to Formulas > Name_Manager > New in Excel. I now want to refer to this named range in a macro that I’m writing. The macro will copy the color of a cell if that cell’s background color matches that of my named range.

I have a couple of files where previously good named ranges stopped working. They appear in name manager with the correct cell reference and value.

In my Excel VBA, I have well over 100 lines of code similar to the following.

I’m trying to use a named range to control cell formats, but it does not appear to work as expected: