Search code examples
excelvbanamed-ranges

Reference Named Ranges in VBA

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.

My macro currently works well when I refer to the cell's location as Range("S2") in the following code:

Trans_ECO_Row.Cells(, 13).Value = Trans_Queue_Row.Cells(, 14).Value
    If Trans_Queue_Row.Cells(, 14).Interior.Color = QueueSheet.Range("S2").Interior.Color Then
        Trans_ECO_Row.Cells(, 13).Interior.Color = Trans_Queue_Row.Cells(, 14).Interior.Color
    End If

However, I've named cell S2 MGRColor by going to Formulas > Name_Manager > New in Excel. How can I replace S2 with MGRColor in my above code?

Solution

  • You can do this using the Range() function. The named range would be the function's only argument, surrounded by double quotes:

    Range("MGRColor")

    You can use this as you would any other range object:

    Range("MGRColor").Value = ...
Range("MGRColor").Interior.Color = ...
' Etc